date range additions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ann - 28 May 2008 22:39 GMT

if column A "text" and column A "more text" and column B date > 5/18 and less
than 5/22 (***date is the current week) then sum number in column C.

basically looking for: based what's in column A, the dates in column B
should be the current week, then add whats in column C

Reply to this Message

Peo Sjoblom - 28 May 2008 22:52 GMT

One way

=SUMPRODUCT(--(A1:A30="a"),--(B1:B30>=TODAY()-7),--(B1:B30<=TODAY()),C1:C30)

adapt to fit your data

Signature

Regards,

Peo Sjoblom

> if column A "text" and column A "more text" and column B date > 5/18 and
> less
> than 5/22 (***date is the current week) then sum number in column C.
>
> basically looking for: based what's in column A, the dates in column B
> should be the current week, then add whats in column C

Reply to this Message

Ann - 28 May 2008 23:10 GMT

close...i need to put in a date, rather than -7. for example i need it to be
=SUMPRODUCT(--(A1:A30="a"),--(B1:B30>=5/18/2008),--(B1:B30<=5/22/2008),C1:C30)

what's the correct syntax for the dates?

Reply to this Message

ND Pard - 28 May 2008 23:27 GMT

In lieu of the 5/18/2008, use the Date(2008,5,18) function, etc.

Good Luck.

> close...i need to put in a date, rather than -7. for example i need it to be
> =SUMPRODUCT(--(A1:A30="a"),--(B1:B30>=5/18/2008),--(B1:B30<=5/22/2008),C1:C30)
>
> what's the correct syntax for the dates?

Reply to this Message

Ann - 29 May 2008 15:27 GMT

here's the formula i'm using.
=SUMPRODUCT(--(H3:H200="ABC"),--(D3:D200>DATE(2008,5,18)),--(D3:D200>=DATE(2008,5,22)),E3:E200)

it's only adding up if the date matches 5/22, not 5/19 through 5/22.
tia

Reply to this Message

Peo Sjoblom - 29 May 2008 15:47 GMT

You need to check the original formula I gave you and use the same
technique, the last date part should be

(D3:D200<=DATE(2008,5,22)

and not

(D3:D200>=DATE(2008,5,22)

Signature

Regards,

Peo Sjoblom

> here's the formula i'm using.
> =SUMPRODUCT(--(H3:H200="ABC"),--(D3:D200>DATE(2008,5,18)),--(D3:D200>=DATE(2008,5,22)),E3:E200)
>
> it's only adding up if the date matches 5/22, not 5/19 through 5/22.
> tia

Reply to this Message

Ann - 29 May 2008 15:59 GMT

thank you so much, don't know when i changed it, that fixed it!

> You need to check the original formula I gave you and use the same
> technique, the last date part should be
[quoted text clipped - 10 lines]
> > it's only adding up if the date matches 5/22, not 5/19 through 5/22.
> > tia

Reply to this Message