Total last 30 days

[Guest]

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?

 

[Peo Sjoblom]

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

 

[Arvi Laanemets]

Hi

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

 

[Guest]

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

 

[Guest]

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)

 

[MartinW]

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

 

[Guest]

Thanks to everyone...

all really great ideas.

Eric