Share via

COUNTA IF

Anonymous
2016-09-19T03:06:51+00:00

So I want to count the number of times a cell within a range is not blank if the cell in the corresponding row of a different range contains a certain value. For example I want to count how many males attended an event on a certain date, so cells F11:F32 contain either "Male" or "Female" then each column has a different date or event so if I want to know the number of males that attended in the range L11:L32 which either contains a value of "x" or "". What I have so far is {=COUNTA(IF(F11:F32="Male",L11:L32,""))} and this returns 22 when the correct number would be 10. If I do not enter it as an array formula it returns a value of 1.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-09-19T03:56:25+00:00

    I hope I have understood your query correctly.

    You want to count the number of cells in range L11:L32 which are not blank and have a value "Male"

    in corresponding cell in range F11:F32.

    Try the formula:

    =COUNTIFS(F11:F32,"Male",L11:L32,"<>"&"")

    NOTE:  This is NOT an ARRAY formula and DOES NOT require a CTRL+SHIFT+ENTER.

    However, if you want to count the number of empty cells in column-L with corresponding cells

    in column-F as "Male" then you can try       =COUNTIFS(F11:F32,"Male",L11:L32,"="&"")

    ( pls see the pic below  the formula is keyed-in in cell I8 )

    Hope you find this Helpful.

    30+ people found this answer helpful.
    0 comments
  2. OssieMac 48,001 Reputation points Volunteer Moderator
    2016-09-19T03:47:35+00:00

    COUNTIF function will count where there is one condition to be met.

    COUNTIFS function where there are multiple conditions to be met.

    However, attempting to compare  "" for a match is a problem and needs a workaround. The following combined use of both functions counts the number of Males disregarding column L and then subtracts the number that matches with "Male" in column F and then match with "x" in column L which leaves the number of males without "x" in column L.

    =COUNTIF(F11:F32,"Male") - COUNTIFS(F11:F32,"Male",L11:L32,"x")

    You could make it easier and just use COUNTIFS function on its own if you replace the "" with another alpha character.

    10+ people found this answer helpful.
    0 comments
  3. Anonymous
    2016-09-20T02:58:24+00:00

    I am pleased that my suggested solutions solved your problem.

    Request you to pls mark it as Answer for benefit of others. Thanks.

    4 people found this answer helpful.
    0 comments
  4. OssieMac 48,001 Reputation points Volunteer Moderator
    2016-09-19T03:59:43+00:00

    Another solution.

    Either of the following COUNTIFS function will handle the double quotes.

    =COUNTIFS(F11:F32,"Male",L11:L32,"""""")

    =COUNTIFS(F11:F32,"Male",L11:L32,CHAR(34) & CHAR(34))

    1 person found this answer helpful.
    0 comments
  5. Anonymous
    2016-09-19T17:51:25+00:00

    Ok so I must have really had my first formula off, which may have made it a little more confusing, but COUNTIFS worked exactly like I wanted it to. 

    The "" in my first formula is what I was trying to return if F11:F32 was not "Male" so that COUNTA would only take the cells that had a value in them, but it doesn't work that way. Since I knew the value would either be x or null I was able to use the following formula to achieve my purpose.

    =COUNTIFS(F11:F32,"Male",L11:L32,"x")  

    Thank you to all who replied.

    0 comments