SumProduct with date and time

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

frogman7 - 17 Aug 2007 23:22 GMT

I have finally found that with my formulas the calulations are based
on the time. Can someone help me find the best way to correct this
without using macros to strip the time out of the data.

Bob    09/08/2007 01:05    13/08/2007 00:00    Bob
Bill    10/08/2007 23:39        Bill
Kyle    11/08/2007 07:36        Kyle
Bob    16/07/2007 20:39
Bill    27/07/2007 19:59
Kyle    18/07/2007 16:51
Bob    14/07/2007 16:31
Bill    21/06/2007 16:46
Kyle    30/06/2007 16:55
Bob    12/06/2007 01:05
Bill    13/06/2007 23:39            1    2    0    1    1
Kyle    14/06/2007 07:36            1    1    1    1    1
Bob    12/01/2007 20:39            1    1    2    0    1
Bill    13/08/2006 19:59
Kyle    14/05/1999 16:51

These are the formulas for the above calcs
SUMPRODUCT(--($A$1:$A$15$D$1),--($B$1:$B$15<$C$1),--($B$1:$B$15>$C
$1-15))
SUMPRODUCT(--($A$1:$A$15$D1),--($B$1:$B$15<$C$1-15),--($B$1:$B$15>$C
$1-30))
SUMPRODUCT(--($A$1:$A$15$D1),--($B$1:$B$15<$C$1-30),--($B$1:$B$15>$C
$1-60))
SUMPRODUCT(--($A$1:$A$15$D1),--($B$1:$B$15<$C$1-60),--($B$1:$B$15>$C
$1-90))
SUMPRODUCT(--($A$1:$A$15$D1),--($B$1:$B$15<$C$1-90))
SUMPRODUCT(--($A$1:$A$15$D$2),--($B$1:$B$15<$C$1),--($B$1:$B$15>$C
$1-15))
SUMPRODUCT(--($A$1:$A$15$D2),--($B$1:$B$15<$C$1-15),--($B$1:$B$15>$C
$1-30))
SUMPRODUCT(--($A$1:$A$15$D2),--($B$1:$B$15<$C$1-30),--($B$1:$B$15>$C
$1-60))
SUMPRODUCT(--($A$1:$A$15$D2),--($B$1:$B$15<$C$1-60),--($B$1:$B$15>$C
$1-90))
SUMPRODUCT(--($A$1:$A$15$D2),--($B$1:$B$15<$C$1-90))
SUMPRODUCT(--($A$1:$A$15$D$3),--($B$1:$B$15<$C$1),--($B$1:$B$15>$C
$1-15))
SUMPRODUCT(--($A$1:$A$15$D3),--($B$1:$B$15<$C$1-15),--($B$1:$B$15>$C
$1-30))
SUMPRODUCT(--($A$1:$A$15$D3),--($B$1:$B$15<$C$1-30),--($B$1:$B$15>$C
$1-60))
SUMPRODUCT(--($A$1:$A$15$D3),--($B$1:$B$15<$C$1-60),--($B$1:$B$15>$C
$1-90))
SUMPRODUCT(--($A$1:$A$15$D3),--($B$1:$B$15<$C$1-90))

Bob    09/08/2007    13/08/2007    Bob
Bill    10/08/2007        Bill
Kyle    11/08/2007        Kyle
Bob    16/07/2007
Bill    27/07/2007
Kyle    18/07/2007
Bob    14/07/2007
Bill    21/06/2007
Kyle    30/06/2007
Bob    12/06/2007
Bill    13/06/2007            1    1    1    1    1
Kyle    14/06/2007            1    1    1    1    1
Bob    12/01/2007            1    1    1    1    1
Bill    13/08/2006
Kyle    14/05/1999

These are the formulas for the above calcs
SUMPRODUCT(--($A$25:$A$39$D$25),--($B$25:$B$39<$C$25),--($B$25:$B$39>$C
$25-15))
SUMPRODUCT(--($A$25:$A$39$D25),--($B$25:$B$39<$C$25-15),--($B$25:$B$39>
$C$25-30))
SUMPRODUCT(--($A$25:$A$39$D25),--($B$25:$B$39<$C$25-30),--($B$25:$B$39>
$C$25-60))
SUMPRODUCT(--($A$25:$A$39$D25),--($B$25:$B$39<$C$25-60),--($B$25:$B$39>
$C$25-90))
SUMPRODUCT(--($A$25:$A$39$D25),--($B$25:$B$39<$C$25-90))
SUMPRODUCT(--($A$25:$A$39$D$26),--($B$25:$B$39<$C$25),--($B$25:$B$39>$C
$25-15))
SUMPRODUCT(--($A$25:$A$39$D26),--($B$25:$B$39<$C$25-15),--($B$25:$B$39>
$C$25-30))
SUMPRODUCT(--($A$25:$A$39$D26),--($B$25:$B$39<$C$25-30),--($B$25:$B$39>
$C$25-60))
SUMPRODUCT(--($A$25:$A$39$D26),--($B$25:$B$39<$C$25-60),--($B$25:$B$39>
$C$25-90))
SUMPRODUCT(--($A$25:$A$39$D26),--($B$25:$B$39<$C$25-90))
SUMPRODUCT(--($A$25:$A$39$D$27),--($B$25:$B$39<$C$25),--($B$25:$B$39>$C
$25-15))
SUMPRODUCT(--($A$25:$A$39$D27),--($B$25:$B$39<$C$25-15),--($B$25:$B$39>
$C$25-30))
SUMPRODUCT(--($A$25:$A$39$D27),--($B$25:$B$39<$C$25-30),--($B$25:$B$39>
$C$25-60))
SUMPRODUCT(--($A$25:$A$39$D27),--($B$25:$B$39<$C$25-60),--($B$25:$B$39>
$C$25-90))
SUMPRODUCT(--($A$25:$A$39$D27),--($B$25:$B$39<$C$25-90))

Reply to this Message

Peo Sjoblom - 17 Aug 2007 23:31 GMT

If you want the dates only from a range where there are both dates and times

--(INT($B$1:$B$15)<$C$1)

will strip off the time

to strip off the dates to compare times only

--(MOD($B$1:$B$15,1)<$C$1)

Signature

Regards,

Peo Sjoblom

>I have finally found that with my formulas the calulations are based
> on the time. Can someone help me find the best way to correct this
[quoted text clipped - 89 lines]
> $C$25-90))
> SUMPRODUCT(--($A$25:$A$39$D27),--($B$25:$B$39<$C$25-90))

Reply to this Message

frogman7 - 18 Aug 2007 00:42 GMT

> If you want the dates only from a range where there are both dates and times
>
[quoted text clipped - 106 lines]
>
> - Show quoted text -

It work but when i apply it to my real data on a seprate sheet i get a
value error.
=SUMPRODUCT(--(INT('All Open USA Assigned'!$D$1:$D$9999)=$A45),--
(INT('All Open USA Assigned'!$AW$1:$AW$9999)>$B$1-15),--(INT('All Open
USA Assigned'!$AW$1:$AW$9999)<=$B$1))
What is wrong with this formula?

Reply to this Message

Dave Peterson - 18 Aug 2007 05:31 GMT

Do you have any text in D1:D9999 or AW1:AW9999.

=int(somecellwithtext)
will return that #value! error

Do you have any errors (#value!'s) in any of those cells? Those errors will
make this formula return an error, too.

<<snipped>>

> It work but when i apply it to my real data on a seprate sheet i get a
> value error.
> =SUMPRODUCT(--(INT('All Open USA Assigned'!$D$1:$D$9999)=$A45),--
> (INT('All Open USA Assigned'!$AW$1:$AW$9999)>$B$1-15),--(INT('All Open
> USA Assigned'!$AW$1:$AW$9999)<=$B$1))
> What is wrong with this formula?

Signature

Dave Peterson

Reply to this Message

frogman7 - 18 Aug 2007 15:40 GMT

> Do you have any text in D1:D9999 or AW1:AW9999.
>
[quoted text clipped - 16 lines]
>
> Dave Peterson

That was it i had the header row in the formula.

Reply to this Message