Formula not adding right
 

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:C200009/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

Formula not adding right
 

="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)

"Rob" wrote in message
...
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:C200009/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

Formula not adding right
 

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

--
Don Guillett
SalesAid Software

"Rob" wrote in message
...
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:C200009/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

Formula not adding right
 

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


"Bob Phillips" wrote:

="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)

"Rob" wrote in message
...
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:C200009/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

Formula not adding right
 

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

"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"Rob" wrote in message
...
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:C200009/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

Formula not adding right
 

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)

"Rob" wrote in message
...
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


"Bob Phillips" wrote:

="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)

"Rob" wrote in message
...
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:C200009/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

Formula not adding right
 

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)

"Rob" wrote in message
...
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


"Bob Phillips" wrote:

="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)

"Rob" wrote in message
...
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:C200009/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