Formula not adding right

[Guest]

I'm trying to add up all the items that started on the current day. Johnny is
the starting point person and the date & time is inserted that he started an
item is recorded next to his name, currently there should be 11 for just
today. So I figured that i'd tweak a code I got help on yesterday to do this,
here's what I have...

="Started Today - " &
SUMPRODUCT((B7:B20000="Johnny")*(C7:C20000<=9/7/2006)) & " Total"

The above code returns 8 so I also tried these variants...

="Started Today - " &
SUMPRODUCT((B7:B20000="Johnny")*(C7:C20000<9/7/2006)) & " Total"
="Started Today - " &
SUMPRODUCT((B7:B20000="Johnny")*(C7:C20000>9/7/2006)) & " Total"
="Started Today - " &
SUMPRODUCT((B7:B20000="Johnny")*(C7:C20000<=NOW())) & " Total"


There's more variants but none of them gives me the result I'm looking for.
Oh, also here's what the date colum looks like, 9/7/2006 9:01:11 AM

Thanks in Advance,
Rob

 

[Bob Phillips]

="Started Today - " &
SUMPRODUCT((B7:B20000="Johnny")*(C7:C20000<=--"2006-09-07")) & " Total"


--
HTH

Bob Phillips

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

 

[Don Guillett]

The easiest way is to put a date in a cell and use that cell
=b1

 

[Guest]

Thanks very much Bob. Your formula gave a result of 56 but I changed it to
this...

="Started Today - " &
SUMPRODUCT((B7:B20000="Johnny")*(C7:C20000>--"9/7/2006")) & " Total"

....and it works wonderfully. Just what in the world does -- mean or do? Why
would it make such a difference?

Thanks so much again!
Ever So Grateful,
Rob

 

[Guest]

That was one of the ways that I tried and it gave the same incorrect results
so far the suggection that Bob made seems to work. Do you think my Excel may
have problems since yours doesn't tally right? If so what can I look at to
make sure?

Thanks for the help,
Rob

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

--
HTH

Bob Phillips

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

 

[Bob Phillips]

I am amazed that my version didn't work, and yours did, unless it was just
the <= as against >.

Simply put, the double unary (--) converts a number string (which is what
that date effectively is) into it's numeric equivalent


--
HTH

Bob Phillips

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