Formula to count unique values with criteria

[kdu.bmartins]

Hi everyone,


Here's my deal: I have a bench of data with some columns and I want to count the unique values (if any value appears more than once, just count it once) respecting a criteria.


Let's imagine this data:

[pre]

_______________________________________________________
A        B         C         D          E        F
CODE     COORP     VALUE     COLUMN     COLUMN   COLUMN
001      ABC       200.00    ...        ...      ...
002      DEF       300.00    ...        ...      ...
001      DEF       150.00    ...        ...      ...
002      ABC       500.00    ...        ...      ...
003      ABC       400.00    ...        ...      ...
004      DEF       100.00    ...        ...      ...
_______________________________________________________

[/pre]

Now, I want to count how many different codes appears in column A (result in this case must be 4) and how many different codes appears in column A with column B being 'DEF' (result in this case must be 3).


No, I don't wanna do that using Pivot Tables. I want Formulas.


Had anyone been through this before?


Waiting for your help,

Kdu.

 

[Luke M]

Yep, we've had several topics on this. If you use the search box in top-right corner for "count unique criteria" you'll find several helpful threads, like this one:

http://chandoo.org/forums/topic/counting-unique-values-if-other-criteria-are-true

 

[Hui]

Kdu


Firstly, Welcome to the Chandoo.org forums.


You can try the following:

=SUM(IF(FREQUENCY(IF(B1:B7="DEF",MATCH(A1:A7,A1:A7,0)),ROW(A1:AB7)-ROW(B1)+1),1))

Ctrl+Shift+Enter

 

[kdu.bmartins]

Thanks Luke and Hui. It just worked as well as I wanted.


I was looking for this formula about a month... LOL aways to avoid Pivot Tables and not reeeeaaally needing to find it, just to study and keep it safe for the right opportunity. And now it came. Thanks again.


Just a doubt? Why don't we treat the negative part of the IF formulas?

 

[Luke M]

If you don't include an argument for the False portion of the IF function, the function automatically fills in a "FALSE". As we only care about the things that match, we don't need to provide a False argument. FALSE won't get counted by the SUM function, and (in other uses) it's ignore by the SMALL/LARGE functions.

 

[kdu.bmartins]

Ok, I imagined that. I knew about the "False-autofill", but thought it would be necessary to use that trick with "--" before the parenthesis to convert the "false" strings in zeros.


Thanks again.