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?

Thanks in advance

Try

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

Could also work using "=SUMIFS"http://office.microsoft.com/en-us/ex...in=HP010342656

Thanks Tony and Bob

Both work perfectly.

Problem solved

If you're going to use countifs, you don't need to test for NOT blank.
It will ignore blanks already.

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

Originally Posted by XOR LX

Not sure if I agree. With A1 blank:
=A1<=TODAY()

But that's not a countifs formula is it?

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.

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.