# Thread: Count matching criterias only if duplicate values are found formula ?

1. ## Count matching criterias only if duplicate values are found formula ?

Hello,

I am wondering how I can do this :
If criterias of column D and F are matching AND value inside a cell of column A and G matches (duplicates) then add a count.

 33 Susan Apple 48 64 Peter Raspberry 35 48 Susan Apple 64 35 Susan Apple 95

So lets say I have something like this :
=COUNTIFS(\$B:\$B;"Susan"; \$C:\$C;"Apple")... The value will be 3. However, I only want the formula to count if the value of a cell in column A and D are duplicates. Since 64 and 48 appears in both A and D, the count should be 2 instead of 3.

What would be the appropriate formula for something like this ?

Thank you!

2. Which version of Excel are you using, and to be clear are you counting the numbers or the names?

3. Originally Posted by Aussiebear
Which version of Excel are you using, and to be clear are you counting the numbers or the names?
Microsoft 365 version, fully updated.

Oopsie, I messed up the table above. I updated it. I am trying to count the Susan + Apple as one count each, as long as number in column A shows up in column D.

33 Susan Apple doesn't count, because 33 isn't in the 4th column.

However,
48 Susan Apple and 35 Susan Apple both counts (total of 2), because 48 and 35 shows up in the fourth column.

4. Something like:

=IFERROR(ROWS(FILTER(C1:C4,(C1:C4="Apple")*(B1:B4="Susan")*(COUNTIF(D1:D4,A1:A4)))),0)

5. Couple of bits to look at on the attached depending on the layout you are looking for.

6. Originally Posted by Aflatoon
Something like:

=IFERROR(ROWS(FILTER(C1:C4,(C1:C4="Apple")*(B1:B4="Susan")*(COUNTIF(D1:D4,A1:A4)))),0)
Thank you so much ! That works great.