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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200606/1

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)

"s2m via OfficeKB.com" <u23063@uwe wrote in message
news:621a74c4eaf63@uwe...
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200606/1

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?
--
Don Guillett
SalesAid Software

"s2m via OfficeKB.com" <u23063@uwe wrote in message
news:621a74c4eaf63@uwe...
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200606/1

I get a count of zero



Don Guillett wrote:
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

--
Message posted via http://www.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 Phillips wrote:
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

--
Message posted via http://www.officekb.com

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.

"s2m via OfficeKB.com" wrote:

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200606/1

--

Dave Peterson