Count Ifs for counting duplicate rows only once combined with multiple criteria

[rhmkrmi]

Hello,

I am trying to use countifs to count column A values for each month in column B without duplicating.

I use this formula and expect to get 3 but it gives me 6.67:
=SUMPRODUCT(1/COUNTIFS(B2:B11,"JUN-21",A2:A11,A2:A11))

Name	Month
John	JUN-21
John	JUN-21
John	JUN-21
John	JUL-21
John	JUL-21
Mary	AUG-21
Mary	JUN-21
Sally	JUN-21
Sally	AUG-21
Sally	AUG-21

Thanks.

 

[Phuoc]

Try this:

=SUMPRODUCT((B2:B11="JUN-21")/COUNTIFS(B2:B11,B2:B11,A2:A11,A2:A11))

 

[rhmkrmi]

Try this:

=SUMPRODUCT((B2:B11="JUN-21")/COUNTIFS(B2:B11,B2:B11,A2:A11,A2:A11))

Perfect, works like a charm.

 

[rhmkrmi]

Try this:

=SUMPRODUCT((B2:B11="JUN-21")/COUNTIFS(B2:B11,B2:B11,A2:A11,A2:A11))

Is this supposed not work when the data ranges increases?
I am using it to check 30000 rows and Excel keep crashing trying to calculate.

Thanks.

 

[Peter_SSs]

@rhmkrmi
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[rhmkrmi]

Perfect, works like a charm.

Is this supposed not work when the data ranges increases?
I am using it to check 30000 rows and Excel keep crashing trying to calculate.

Thanks.

Try this:

=SUMPRODUCT((B2:B11="JUN-21")/COUNTIFS(B2:B11,B2:B11,A2:A11,A2:A11))

Just a question on #2 above from Phuoc.
Is this supposed not to work when the data range increases?
I am using it to check 30000 rows and Excel keep crashing trying to calculate.

Thank you.

 

[Peter_SSs]

Thanks for updating your profile.

See how this goes

=IFNA(ROWS(UNIQUE(FILTER(A2:A30000,B2:B30000="JUN-21",NA()))),0)