Total last 30 days

E

Eric

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?
 
P

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

David

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

David

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top