Sum of past 30 days of data

**drummerdickens** · 09-09-2011, 06:24 PM

Howdy,

First time poster here slowly going insane at my inability to do what I initially thought would be simple, but apparently is not.

Column A has a list of dates
Column B has a list of values

I want one single cell to show me what the total of the past 30 days worth of values are based on today's date, allowing me to enter a corresponding value every day and for the cell to automatically calculate the total value for the past 30 days.

I feel like I've tried everything, but nothing wants to work. Any ideas?

**MarvinP** · 09-09-2011, 06:31 PM

Hi drummerdickens and welcome to the forum.

I believe this is a job for the SumIf function.
http://office.microsoft.com/en-us/ex...005209292.aspx

See the attached with an example.

**drummerdickens** · 09-09-2011, 07:21 PM

I love you. I really do.

Turns out I had managed to do this, except my dummy data I was using to test it stretched PAST todays date, and that formula starts adding up not only the past 30 days of data but anything after todays date too!

It wasn't until I saw that you did the same thing that realised it must've been a mistake my end with the data. I really appreciate it, good stuff bro.

**MarvinP** · 09-09-2011, 07:26 PM

Turn all that love into a "Pay it forward" event. Help others and that's enough for me.

**drummerdickens** · 09-21-2011, 12:40 PM

OK. I appreciate this is somewhat of a bump but I thought it would make more sense to ask this question here in case anyone ever has the same problem and goes searching for it.

I have a new problem with this formula; the department I have given this to have decided they want to input future dates and their values. This is where the formula falls down; the formula will add all values for the past 30 days AND any future dates.

They require a formula that ignores both the values 31 days and further and anything values in the future (values past today's date).

I have literally no idea how to go about this. Any ideas would amaze me, and be appreciated!

**arthurbr** · 09-21-2011, 12:59 PM

Maybe =SUMPRODUCT((A2:A344>TODAY()-30)*(A2:A344<TODAY()+1)*B2:B344)

Adapt the range to suit your needs

**drummerdickens** · 09-21-2011, 01:16 PM

You did it. You bloody well did it.

Tell me chaps, how do you get so good at Excel? Did you take courses, or are there any books or websites I can peruse to really up my standard? Or was it out of necessity (much like what I'm facing) where you are given a problem and you just have had to find a way to do it browsing the internet?

Fantastic stuff lads.

**arthurbr** · 09-22-2011, 05:48 AM

XL, as our forum, has rules that can be best learned by practice. To start :

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save