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))
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))
- Thanks very much for the response, appreciated.
