count blank dates

  • Thread starter s2m via OfficeKB.com
  • Start date
S

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
 
B

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)
 
D

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?
 
S

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



Bob said:
Try this

SUMPRODUCT(--(TCS!$Z$2:$Z$1000<=--"2006-05-31"),--(TCS!AA2:AA1000=""))
I need to count in 2 columns this condition.
Click to expand...
[quoted text clipped - 9 lines]
Sharon
Click to expand...
Click to expand...
 
D

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.
 
Top