Forum Discussion
How to COUNTIFS while excluding repetitions
Hello,
I have a three column of data, name, gender and age. I have used COUNTIFS to count the number of people with certain gender that are above a certain age. The only caveat is that I have repetition in my list. What do you suggest I add to my code to exclude the repetitions?
Thanks in advance for the help.
Ali
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))
4 Replies
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
- Thanks @IIlirU for the response and the example, highly appreciated.
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.
