# Distinct Count Ifs Formula

##### New Member
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.

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### Eric W

##### MrExcel MVP
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.

##### New Member
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

##### Well-known Member
Hi,

You can test the following formula
Code:
``=SUMPRODUCT((B2:B6=25)*(C2:C6="Red"))``

HTH

#### Canapone

##### Active Member
Hi

Code:
``=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

##### MrExcel MVP
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))

Replies
9
Views
206
Replies
1
Views
499
Replies
1
Views
221
Replies
10
Views
1K
Replies
6
Views
214

1,191,697
Messages
5,988,163
Members
440,131
Latest member
EricMoz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back