Question: I have values in two not adjacent columns. I want to know how many unique distinct values there are in those two columns combined?
Formula in C12:
=SUM(1/COUNTIF(List1, List1))+SUM(IF(COUNTIF(List1, List2)=0, 1/COUNTIF(List2, List2), 0))
How to create an array formula
- Copy formula
- Select cell C12
- Paste formula in formula bar
- Press and hold Ctrl + Shift
- Press Enter
What is named ranges?
How to customize the formula to your excel workbook
Change the named ranges.
Download excel example file
(Excel 97-2003 Workbook *.xls)
Functions used in this blog post:
Counts the number of cells within a range that meet the given condition
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
Adds all the numbers in a range of cells