Forum Discussion
How to COUNTIFS while excluding repetitions
If you have a version of Excel 365 that includes the new FILTER and UNIQUE versions, you can use a formula such as
=SUM(--(LEN(UNIQUE(FILTER(A2:A100,(B2:B100="F")*(C2:C100>65))))>0))
where A2:A100 is the name column, B2:B100 is the gender column, and C2:C100 is the age column.
The formula will return the count of unique names with gender "F" and age above 65.
If you have an older version of Excel, you can use the following array formula confirmed with Ctrl+Shift+Enter:
=SUM(--(FREQUENCY(IF((B2:B100="F")*(C2:C100>65),MATCH(A2:A100,A2:A100,0)),ROW(A2:A100)-ROW(A2)+1)>0))
Hi,
I'm giving to you an example, maybe it helps you.
See the printscreen above and put in cell F2 criteria of age and in cell G2 apply this formula:
=SUM(IFERROR((FREQUENCY(MATCH(B2:B15&A2:A15,B2:B15&A2:A15,0),ROW(A2:A15)-ROW(A2)+1)>0)*(C2:C15>=F2),0))
This is an array formula so you should confirm it with Ctrl+Shift+Enter, and not just with Enter.
Hope this hepls.
Regards,
IlirU
