How to SUM a range of data based on dates?

[watzupmark]

Hi,


I have an excel spreadsheet I use to track my monthly household budget and expenses. Each month I paste my bank statement data into the spreadsheet and then manually point my my dashboards date to the correct month's date range of data. This is tedious. There must be a way to automatically detect the date range and return the correct values per category. I have tried to explain below as best I can what I have...


My data is as follows:


SHEET 1

COL C - contains Categories (i.e. Salaries, Entertainment, Food etc)

COL D - contains Date range (per day i.e. 2012/05/24)

COL E - contains Amount (i.e. 360.58)


SHEET 2 (looks at sheet one raw data and pulls in per month, which I manually change each month)

COL C - contains a list of Categories (which I use to 'lookup' the correct category value)

COL D - contains Sum Values for that month (i.e. May 2012)


What formula can I write that can look at the entire Date Range in Sheet 1 and return ONLY the total values per category, for a particular month? I am happy to amend the structure of the spreadsheet as well...


Many thanks

Mark

 

[bobhc]

Good day watzupmark


A Pivot Table would do all you require, just use the data on sheet one to make it and that's it. Display the data as you want

 

[keymaster]

Hi watzupmark


Welcome to Chandoo.org forums and thanks for your question.


As Bob suggested, using a pivot is easy and quick option. If you insist on using formulas then follow below steps.


1. In Sheet 2, in an empty cell (say A1) write =MAX(Sheet1-date-range)

This will give you maximum (ie latest) date.

2. In next cell (say A2) use something like =DATE(year(A1), Month(A1),1)

3. In next cell (say A3) use something like =EOMONTH(A2,0)


Now A2 & A3 will have latest month's start and end dates.


Next use SUMIFS to sum up values between those dates (looks like you have already go this covered).


See these pages for some help:


http://chandoo.org/wp/2012/06/14/use-max-to-find-latest-date-in-a-list/

http://chandoo.org/wp/2011/09/27/sum-between-2-dates/

 

[watzupmark]

Thank you guys - will take a look and see if the above helps! Will report back... Christmas and all that is stopping me at moment from spending good quality time on this.


Cheers

 

[watzupmark]

Thanks guys - but turns out the SUMPRODUCT formula was what I needed. Ended up with this being my forumula which solved my problems...


=SUMPRODUCT(--(STATEMENTS!$C$3:$C$10000=$D4),--(STATEMENTS!$D$3:$D$10000>=E$3),--(STATEMENTS!$D$3:$D$10000<DATE(YEAR(E$3),MONTH(E$3)+1,DAY(E$3))),STATEMENTS!$E$3:$E$10000)