count blank dates

[s2m via OfficeKB.com]

I need to count in 2 columns this condition.

Column Z =< 5/31/06

Column AA = blank date fields

I've tried this but it does not work

=SUMPRODUCT(--(TCS!$Z$2:$Z$1000<="5/31/06"),--(TCS!AA2:AA1000=""))

Much thanks

Sharon

 

[Bob Phillips]

Try this

SUMPRODUCT(--(TCS!$Z$2:$Z$1000<=--"2006-05-31"),--(TCS!AA2:AA1000=""))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Don Guillett]

Always nice to tell us what
"it does not work"
means. "It all depends on your definition of IS is"
maybe you have the range set to too many rows?

 

[s2m via OfficeKB.com]

I get a count of zero

Always nice to tell us what
"it does not work"
means. "It all depends on your definition of IS is"
maybe you have the range set to too many rows?

I need to count in 2 columns this condition.

[quoted text clipped - 9 lines]

Sharon

 

[s2m via OfficeKB.com]

So the way the date is enterd matters?
Wow

So much to learn and so little time, lol

Did you see my other post about the filter count only working if i turn off
Auto Calc?

Thanks

Sharon

Try this

SUMPRODUCT(--(TCS!$Z$2:$Z$1000<=--"2006-05-31"),--(TCS!AA2:AA1000=""))

I need to count in 2 columns this condition.

[quoted text clipped - 9 lines]

Sharon

 

[Dave Peterson]

I find this easier to read:

=SUMPRODUCT(--(TCS!$Z$2:$Z$1000<=date(2006,5,31)),--(TCS!AA2:AA1000=""))

But this: "5/31/06" is just a string--not a date.

Bob likes to use: --"5/31/06" that coerces that string into a number and dates
are just numbers nicely formatted to excel.