Count matching criterias only if duplicate values are found formula ?

[MasterBash]

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!

[Aussiebear]

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

[MasterBash]

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.

[Aflatoon]

Something like:


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

[georgiboy]

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

[MasterBash]

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. :)