# Exclude blank cells from COUNTIFS formula

1. ## Exclude blank cells from COUNTIFS formula

Hello experts

I have a formula that counts the number of entries within a table based on two conditions

Condition 1 - Column I = "No"
Condition 2 - Column G = Less than 6 months

I've got a formula as below which works except it's also counting blank cells where the data has yet to be entered.

=SUM(IF(I27:I47="No",IF(G27:G47<=TODAY()-180,1,0)))

Can anyone advise how I insert 'don't count blank cells' into this formula?

2. ## Re: Exclude blank cells from COUNTIFS formula

One way...

Normally entered:

=SUMPRODUCT(--(I27:I47="No"),--(G27:G47<>""),--(G27:G47<=TODAY()-180))

3. ## Re: Exclude blank cells from COUNTIFS formula

Try

=COUNTIFS(I27:I47,"No",G27:G47,"<>",G27:G47,"<="&TODAY()-180)

5. ## Re: Exclude blank cells from COUNTIFS formula

Thanks Tony and Bob

Both work perfectly.

Problem solved

6. ## Re: Exclude blank cells from COUNTIFS formula

You're welcome. We appreciate the feedback!

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

7. ## Re: Exclude blank cells from COUNTIFS formula

If you're going to use countifs, you don't need to test for NOT blank.

=COUNTIFS(I27:I47,"No",G27:G47,"<="&TODAY()-180)

8. ## Re: Exclude blank cells from COUNTIFS formula

Originally Posted by Jonmo1
If you're going to use countifs, you don't need to test for NOT blank.

=COUNTIFS(I27:I47,"No",G27:G47,"<="&TODAY()-180)
Not sure if I agree. With A1 blank:

=A1<=TODAY()

will always return TRUE.

Regards

9. ## Re: Exclude blank cells from COUNTIFS formula

Originally Posted by XOR LX
Not sure if I agree. With A1 blank:
=A1<=TODAY()
But that's not a countifs formula is it?

10. ## Re: Exclude blank cells from COUNTIFS formula

Originally Posted by Jonmo1
But that's not a countifs formula is it?
Apologies. You're correct.

Regards

11. ## Re: Exclude blank cells from COUNTIFS formula

No worries.

I suppose we shouldn't say that countifs straight up ignores blanks.
It does for Inclusive numerical comparisons (the value IS less than x)..
But not for Exclusive (the value is NOT equal to)
"<>Hello" or "<>10" Will count blanks.

12. ## Re: Exclude blank cells from COUNTIFS formula

Originally Posted by Jonmo1
No worries.

I suppose we shouldn't say that countifs straight up ignores blanks.
It does for Inclusive numerical comparisons (the value IS less than x)..
But not for Exclusive (the value is NOT equal to)
"<>Hello" or "<>10" Will count blanks.
Interesting stuff.

I have to say that I find it a tad strange (not to mention practically difficult!) that there are all these seeming inconsistencies between functions when it comes to comparisons, particularly with regards to blanks.

Cheers

13. ## Re: Exclude blank cells from COUNTIFS formula

Originally Posted by XOR LX
there are all these seeming inconsistencies between functions when it comes to comparisons, particularly with regards to blanks.
Yep, and don't get me started on COUNTA vs COUNTBLANK with regards to formula blanks.
COUNTA will consider ="" as NOT Blank
COUNTBLANK will consider ="" as Blank.

Ugh...

I'll accept it either way, but both should be the same.

14. ## Re: Exclude blank cells from COUNTIFS formula

Originally Posted by Jonmo1
Yep, and don't get me started on COUNTA vs COUNTBLANK with regards to formula blanks.
COUNTA will consider ="" as NOT Blank
COUNTBLANK will consider ="" as Blank.

Ugh...
LOL!! Agreed - a nightmare!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1