Comparing two columns and sum unique values using array formula in excel
Array Formula in A15:
=SUM(IF(COUNTIF(List1, List1)+COUNTIF(List2, List1)=1, List1)+IF(COUNTIF(List2, List2)+COUNTIF(List1, List2)=1, List2)) + CTRL + SHIFT + ENTER
Named ranges
List1 (A2:A11)
List2 (B2:B11)
What is named ranges?
Download excel sample file for this tutorial.
Comparing two columns and sum unique values.xls
(Excel 97-2003 Workbook *.xls)
Functions in this article:
COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition
SUM(number1,[number2],)
Adds all the numbers in a range of cells
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
Related blog posts
- Filter duplicates from two columns combined and sort from A to Z using array formula in excel
- Identify missing values in two columns using excel formula
- Sort text cells alphabetically from two columns using excel array formula
- Extract a unique distinct list from two columns using excel 2007 array formula
- Filter common values from three columns 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