Excel formula: count of unique cells in column, IF corresponding row is certain value

3
votes

Currently I have a formula that just counts the unique values in a column:

=SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1))

However, I'd only like to count them if a corresponding cell in the same row has a certain value. Is this possible without using VBA?

For example:

     A        B

     1       Old
     1       Old
     2       New
     2       New
     3       Old
     3       New
     4       New

I want to get the count of unique values of the A cells that correspond to the "New" value in the B col. So the unique occurrence count in A for "New" would be 3

Have been looking over excel references and not seeing how I would do this with ranges of data.

1
excelexcel-formula

1 Answers

3
votes

You can use this formula

=SUM(IF(FREQUENCY(IF(B2:B10="New",A2:A10),A2:A10),1))

confirmed with CTRL+SHIFT+ENTER