Criteria Based Unique Records

[gehusi]

Hi everyone, I'm looking for a formula that will return the number of unique records that correspond to a separate criteria. As an example:

Column A Column B
Apple 123
Banana 123
Apple 101
Car 122
Banana 101
Apple 145
Apple 123


If the criteria value was "Apple", the formula should return 3 (123, 101, and 145). If it was "Banana", it would return 2 (123, 101).

Thanks in advance for your help!

 

[Saagar]

I would use a pivot table. Did you try that?

Once you set-up a pivot table, you can update the pivot filter with the criteria you want, and the table will return the unique values.

 

[T. Valko]

Hi everyone, I'm looking for a formula that will return the number of unique records that correspond to a separate criteria. As an example:

Column A Column B
Apple 123
Banana 123
Apple 101
Car 122
Banana 101
Apple 145
Apple 123


If the criteria value was "Apple", the formula should return 3 (123, 101, and 145). If it was "Banana", it would return 2 (123, 101).

Thanks in advance for your help!

If the data in column B really is numbers...

Array entered**:

=SUM(IF(FREQUENCY(IF(A2:A8="apple",B2:B8),B2:B8),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[T. Valko]

If the data in column B really is numbers...

Array entered**:

=SUM(IF(FREQUENCY(IF(A2:A8="apple",B2:B8),B2:B8),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

If the data in column B might not be numbers then here's a generic version (counts both text and numbers).

Still array entered:

=SUM(IF(FREQUENCY(IF(A2:A8="apple",MATCH(B2:B8,B2:B8,0)),ROW(B2:B8)-ROW(B2)+1),1))

Assumes no empty cells within the data range of column B.

 

[Saagar]

Sorry - misunderstood your post. Valko is right.

Also, see this to learn more -
http://office.microsoft.com/en-us/ex...que_values_by_

 

[gehusi]

Thanks Valko, that worked.

 

[T. Valko]

Thanks Valko, that worked.

You're welcome. Thanks for the feedback!