Distinct Count Ifs Formula

[madab]

Hi,

Have started using Sumifs and Countifs formulas recently and have found quite useful. When using Coutifs formula though, is there a Distinct Count version?

I have a data set that contains a list of people but some may appear multiple times. Each person has a unique identifier and I can't remove the multiple lines, I need to keep them in the data.

I want to be able to count how many people have a specific set of conditions within the data. I can use Countifs formula but that doesn't get around the individuals appearing more than once and is throwing off calculations when trying to work out averages.

Any advise much appreciated!

 

[Eric W]

COUNTIFS can't count unique instances, but there are other options.

Could you provide a small sample of your table, and explain what conditions you want to check, and what results you want to see? You can possibly use the HTML Maker link in my signature to show a table.

 

[madab]

Hi Eric,

Table below is a bit simplified but I think the logic is the same ....

Id Age Colour
1 25 Red
2 26 Blue
3 25 Red
1 25 Red
4 26 Yellow

I want to count the number of Id's that are 25 and Red. I think Countif would give me 3 but there are only 2 because Id 1 appears twice in the table. Does that make sense?

 

[James006]

Hi,

You can test the following formula

=SUMPRODUCT((B2:B6=25)*(C2:C6="Red"))

HTH

 

[Canapone]

Hi

=SUM(IF(FREQUENCY(IF(C2:C6="Red",IF(B2:B6=25,A2:A6)),A2:A6),1))

to be confirmed with control+shift+enter

Regards

 

[Eric W]

Canapone's formula is pretty much ideal if the ID values in column A are numeric. If they are alphanumeric, try:

=SUM(IF(FREQUENCY(IF(C2:C6="Red",IF(B2:B6=25,MATCH(A2:A6,A2:A6,0))),ROW(A2:A6)-ROW(A2)+1),1))