Forum Discussion

AliMosayyebi's avatar
AliMosayyebi
Copper Contributor
Mar 05, 2021
Solved

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 rep...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Mar 05, 2021

    AliMosayyebi 

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

IlirU's avatar
IlirU
Brass Contributor
Mar 05, 2021

 

 

AliMosayyebi 

 

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

  • AliMosayyebi's avatar
    AliMosayyebi
    Copper Contributor
    Mar 07, 2021
    Thanks @IIlirU for the response and the example, highly appreciated.

Resources