having trouble with countif and 2 criteria (date AND nonblank?)

[bran987]

Hello Everyone

So I have my countif if past a certain date, which works fine:

=COUNTIF(J89:J128,">="&Sheet1!$A$2)

but what I need is this...

How do I count... but only if a date is past a certain range AND the cell next to it is nonblank?


Thanks so much for any help.

 

[TheNoocH]

Try this...
=sumproduct(--(J89:J128>=Sheet1!$A$2),--(K89:K128<>""))

 

[bran987]

Try this...
=sumproduct(--(J89:J128>=Sheet1!$A$2),--(K89:K128<>""))

Hmm..

Doesn't seem to have quite worked.

So for instance just pretend:

ColumnA ColumnB
3/15/08 notblank
3/30/08
4/15/08 notblank
4/30/08


ColumnA/B Row 1 wouldn't be counted because 3/15/08 has already passed (even though it's notblank)
ColumnA/B Row 2 wouldn't count because 3/30/08 has already passed (and it's blank)
ColumnA/B Row 3 wouldn't count because even though 4/15/08 is in the future, the cell next to it is already filled
ColumnA/B Row 4 WOULD count because 4/30/08 hasn't passed and the cell next to it is not filled


So the count would be 1 (only one date is still in the future and has a blank cell next to it).

I hope that helps thanks so much for your effort!

 

[TheNoocH]

Book1
	A	B	C	D
1	3/15/2008	notblank		1
2	3/30/2008
3	4/15/2008	notblank
4	4/30/2008
Sheet1

 

[bran987]

Book1
	A	B	C	D
1	3/15/2008	notblank		1
2	3/30/2008
3	4/15/2008	notblank
4	4/30/2008
Sheet1

I really appreciate your help, but when I add another row to that formula I still get the result of 1, instead of a running count

So I added another row
5/15/08

and changed the formula to:

=SUMPRODUCT(--(A1:A5>=TODAY()),--(B1:B5<>""))

?

but the result was still 1, not 2?

 

[TheNoocH]

change it to the following if you meant to say where the cell next to it equaled blanks
=SUMPRODUCT(--(A1:A5>=TODAY()),--(B1:B5=""))

NOTE: based on original post you had

AND the cell next to it is nonblank?

 

[bran987]

change it to the following if you meant to say where the cell next to it equaled blanks
=SUMPRODUCT(--(A1:A5>=TODAY()),--(B1:B5=""))

NOTE: based on original post you had

Wow you are completely correct. I apologize for getting that backwards.

Your formula does exactly what I need. THANK YOU!

 

[TheNoocH]

you can also try this...not sure which way is "preferred"...both should work...

=SUMPRODUCT(--(A1:A5>=TODAY()),--(ISBLANK(B1:B5)))

 

[Jonmo1]

not sure which way is "preferred"...both should work

I don't think either is "preferred", but there is a difference. The particular situation well determine which one to use...

Here's the difference, and it's a big difference.

="" WILL count Formulas that result in ""
ISBLANK WILL NOT count Formulas that resut in ""

 

[TheNoocH]

jonmo1...thanks for the clarification...that is a big difference to note....thanks...