Total last 30 days
 

I have dates in column A
Numbers in column B

I want to find the sum of numbers on column B that correspond to the last 30
days (including today), 90 days, 6 months, 1 year.

I used
=SUMIF(A:A,(TODAY()-30),B:B)+SUMIF(A:A,(TODAY()-29),B:B)+SUMIF(A:A,(TODAY()-28),B:B)... etc. until I got 30 days.

Q? Is there an easier formula/method?

Total last 30 days
 

=SUMPRODUCT(--(A2:A1000=TODAY()-30),--(A2:A1000<=TODAY()),B2:B1000)

adapt to fit for the rest of the dates


Note that you cannot use the whole column A:A unless you use Excel 2007
--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)


"Eric" wrote in message
...
I have dates in column A
Numbers in column B

I want to find the sum of numbers on column B that correspond to the last
30
days (including today), 90 days, 6 months, 1 year.

I used
=SUMIF(A:A,(TODAY()-30),B:B)+SUMIF(A:A,(TODAY()-29),B:B)+SUMIF(A:A,(TODAY()-28),B:B)...
etc. until I got 30 days.

Q? Is there an easier formula/method?

Total last 30 days
 

Hi

=SUMIF($A:$A,"=" & (TODAY()-30,$B:$B)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"Eric" wrote in message
...
I have dates in column A
Numbers in column B

I want to find the sum of numbers on column B that correspond to the last
30
days (including today), 90 days, 6 months, 1 year.

I used
=SUMIF(A:A,(TODAY()-30),B:B)+SUMIF(A:A,(TODAY()-29),B:B)+SUMIF(A:A,(TODAY()-28),B:B)...
etc. until I got 30 days.

Q? Is there an easier formula/method?

Total last 30 days
 

=SUM(SUMIF(A:A,(TODAY()-{30,29,28,27,26,25,24,23,22,21,20,19,18,17,16,15,1 4,15,14,13,12,11,10,9,8,7,6,5,4,3,2,1,0}),B:B))

"Eric" wrote:

I have dates in column A
Numbers in column B

I want to find the sum of numbers on column B that correspond to the last 30
days (including today), 90 days, 6 months, 1 year.

I used
=SUMIF(A:A,(TODAY()-30),B:B)+SUMIF(A:A,(TODAY()-29),B:B)+SUMIF(A:A,(TODAY()-28),B:B)... etc. until I got 30 days.

Q? Is there an easier formula/method?

Total last 30 days
 

for 30dayts/1 month - =SUM(SUMIF(A:A,"="&(TODAY()-30),B:B)) or
=SUMIF(A:A,"="&DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())),B:B)

for 90 days/3 months - =SUM(SUMIF(A:A,"="&(TODAY()-90),B:B)) or
=SUMIF(A:A,"="&DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY())),B:B)

for 180 days/ 6 months - =SUM(SUMIF(A:A,"="&(TODAY()-182),B:B)) or
=SUMIF(A:A,"="&DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY())),B:B)

for 365 days/1 year - =SUM(SUMIF(A:A,"="&(TODAY()-365),B:B)) or
=SUMIF(A:A,"="&DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),B:B)

"Eric" wrote:

I have dates in column A
Numbers in column B

I want to find the sum of numbers on column B that correspond to the last 30
days (including today), 90 days, 6 months, 1 year.

I used
=SUMIF(A:A,(TODAY()-30),B:B)+SUMIF(A:A,(TODAY()-29),B:B)+SUMIF(A:A,(TODAY()-28),B:B)... etc. until I got 30 days.

Q? Is there an easier formula/method?

Total last 30 days
 

Hi Eric,

If you have multiple entries on the same dates then Peo's formula
will work admirably. If you only have one entry per day then you
are overcomplicating things.

In that situation all that is needed is to put =SUM(B1:B30) into
C30 and drag it down to the end of your data. If you want to drag
it past the end of your data to allow for future input, then make it
=IF(B30="","",SUM(B1:B30)

HTH
Martin

Total last 30 days
 

Thanks to everyone...

all really great ideas.

Eric

"MartinW" wrote:

Hi Eric,

If you have multiple entries on the same dates then Peo's formula
will work admirably. If you only have one entry per day then you
are overcomplicating things.

In that situation all that is needed is to put =SUM(B1:B30) into
C30 and drag it down to the end of your data. If you want to drag
it past the end of your data to allow for future input, then make it
=IF(B30="","",SUM(B1:B30)

HTH
Martin