Share via

Exclude blank cells from COUNTIFS formula

Anonymous
2013-06-20T19:00:31+00:00

Hello,

I'm currently using the COUNTIFS formula to give me a count based on two criterias. The formula I'm using is "=COUNTIFS(TRACKING!$P:$P,">""",TRACKING!$D:$D,Dashboard!D5)"

Column D on the tracking sheet contains a list of numbers (43, 16, 45, 46, etc) and column P contains a date or comment. What I'm trying to get is a count if Column D contains the value of say "43" and data is entered on column P. With the formula I'm using above, I get a count of 1 even if I have 43 appear 7 times in column D with data entered in colum P.

I've tried the following variations in my formula to ignore blank cells in column P:  "<>", "<>""",">0". Nothing is getting me the correct count. Is there anything else I need to add?

Thanks

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

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-06-20T19:39:32+00:00

    Hi,

    The correct formula is this

    =COUNTIFS(Tracking!$P:$P,"<>""",Tracking!$D:$D,Dashboard!D5)

    If it doesn't returning the correct count you need to check what's in column P. Are you sure the cells are empty and don't contain an invisible character like a space?

    8 people found this answer helpful.
    0 comments
  2. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2013-06-20T23:43:40+00:00

    Hi,

    Try this

    =SUMPRODUCT((LEN(TRACKING!$P$2:$P$100)>0)*(TRACKING!$D$2:$D$100=Dashboard!D5))

    Does this work?

    2 people found this answer helpful.
    0 comments
  3. Anonymous
    2013-06-20T19:48:02+00:00

    Hello Mike,

    Thanks for the quick response. I did notice earlier that my calculation options where set to "Manual". I changed it to "Automatic" and now works.

    I tried changing the formula to "<>"""; however, it is giving me the total count of cases that have the number "43" under column D regardless of column P having data or being blank. It seems to only give me the accurate count if I stick to ">"""

    I've checked that my blank cells don't have any data and that there are no invisible characters in place. Any thoughts?

    2 people found this answer helpful.
    0 comments
  4. Anonymous
    2013-08-07T07:47:19+00:00

    If you create a column that is =LEN(P1), say in column Q, and then do the formula this way

    =COUNTIFS(Tracking!$Q:$Q,">0",Tracking!$D:$D,Dashboard!D5) it should have the results you are looking for.

    1 person found this answer helpful.
    0 comments
  5. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2013-08-07T07:51:54+00:00

    I guess you wanted to reply to myer84.

    0 comments