Count unique values in two lists combined in excel
Unique values
Unique values are values existing only once in a list or range. See picture below.
Count unique values in two lists combined
Array formula in D12:
=SUM(IF(COUNTIF(List1, List1)+COUNTIF(List2, List1)=1, 1))+SUM(IF(COUNTIF(List2, List2)+COUNTIF(List1, List2)=1, 1)) + CTRL + SHIFT + ENTER
Named ranges
List1 (B3:B8)
List2 (D3:D7)
What is named ranges?
How to customize the formula to your excel workbook
Change the named ranges.
Download excel example file
Count-unique-values-in-two-columns.xls
(Excel 97-2003 Workbook *.xls)
Functions used in this blog post:
COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition
IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
SUM(number1,[number2],)
Adds all the numbers in a range of cells
Related blog posts
- Count unique distinct values in three columns combined in excel
- Count unique values and unique distinct values in two ranges combined
- Count matching cell values in two columns in excel
- Count unique values and unique distinct values in three ranges combined in excel
- Comparing two columns and sum unique values using array formula in excel
Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.Don´t forget to add my
Leave a Reply