Need help with SUM functions based on dates and other criterias

[MrHaugen]

Hi. I'm trying to get full control over my expenses. I want to be able to view expenditures depending on year, month(s) and type of expense.

This is a simplification of my data:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]04.01.2013
[/TD]
[TD]340
[/TD]
[TD]Transportation
[/TD]
[TD]All
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]10.02.2014
[/TD]
[TD]460
[/TD]
[TD]House
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]23.02.2014
[/TD]
[TD]200
[/TD]
[TD]Food
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]17.03.2013
[/TD]
[TD]340
[/TD]
[TD]Power
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]24.03.2014
[/TD]
[TD]500
[/TD]
[TD]House
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]24.03.2014
[/TD]
[TD]25
[/TD]
[TD]Transportation
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]01.07.2014
[/TD]
[TD]123
[/TD]
[TD]Food
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]21.08.2013
[/TD]
[TD]320
[/TD]
[TD]Cloths
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]25.08.2014
[/TD]
[TD]105
[/TD]
[TD]Transportation
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]28.08.2014
[/TD]
[TD]150
[/TD]
[TD]Power
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Input field:
D1. Month values: All, 1-12
MONTH function can only use 1 to 12 for January to December, but I would like to also select the sum of the whole year. So this is what I've come up with so far.


=IF(D1="All";(SUMPRODUCT((YEAR(A1:A10)=2014)*(B1:B10)));(SUMPRODUCT((YEAR(A1:A10)=2014)*(MONTH(A1:A10)=D1)*( B1:B10))))

This gives me the sum (1563) for the whole year of 2014. It also gives me value for individual months if needed. But I have left out the Expense type here. So, I tried to add this.


=IF(D1="All";SUMPRODUCT((YEAR(A1:A10)=2014)*(SUMIF(C1:C10;"Power";B1:B0)));(SUMPRODUCT((YEAR(A1:A10)=2014)*(MONTH(A1:A10)=D1)*( B1:B10))))

This gives me the value of 3430. Instead of adding the numbers, it seems like there is some multiplication going on here. It should give me the value 150. Preferably.


I could of-course add two sheets. One for the whole year, and one for individual months. But I would very much like to have just one sheet for the whole thing. My head is spinning. Hopefully some of you have some suggestions on how to handle this.

Thanks!

 

[RonB1111]

The best approach would be Pivot Table.
1. Put header names at the top of each data column
2. Select Columns A thru C
3. From the Ribbon, click Insert, PivotTable, OK
4. Drag ExpenseType to the rows section,
5. Drag Amount to the values section (make sure it says "Sum of Amount"
6. Drag Date to the columns section
7 To Group the dates, select any date cell in the pivot table, then right-click "Group", then highlight both Year and Month, then Ok.

Here's a link about pivot tables: Excel Pivot Tables - Easy Excel Tutorial

 

[MrHaugen]

Wow. That was a pretty neat tool. Easy to use as well. Thanks for the tip!

I'm gonna run into a couple of problems with this method though. I pull values from more than one sheet. And the formulas are a bit different, depending on the type. And the grouping is a bit awkward to use in this case. I would much prefer to have two dropdown lists with Year and Month. It's a bit easier to use, and it will probably be easier to use those numbers again in other parts of my document.

Is there any way to fix the formula I tried? It would probably solve all my problems.

 

[RonB1111]

While I'd still prefer the pivot table in your situation, it does take some time playing with it to be able to quickly vary the displayed results. So here's an approach that will both provide a working formula you're looking fo,r as well as make the pivot table easier to use.

Assuming row 1 contains headers, add 2 more columns.
---in D1 enter "Year" and in D2 enter this formula and copy down: =YEAR(A2)
---in E1 enter "Month" and in E2 enter this formula and copy down: =MONTH(B2)

Now enter the following in Columns J, K, L and M where row 1 is just reference descriptors and row 2 is variable criteria input:

[TABLE="width: 467"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]year[/TD]
[TD]start month[/TD]
[TD]end month[/TD]
[TD]Expense Type[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2014[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]power[/TD]
[/TR]
</tbody>[/TABLE]

You'll notice I've added both a Start Month and an End month so you'll be able to summarize by a range of months as well as a single month. A single month, say May, would be 5 in the Start Month and 5 in the End Month.

This would be the formula you're looking for:

=SUMIFS(B:B,D:D,J2,E:E,">="&K2,E:E,"<="&L2,C:C,M2)

For an improved Pivot Table, create a new pivot table based on A1:E11, drag the Year and the Month fields into the "Filters" section, the ExpenseType to the rows section and Amount to the Values section. Don't use the Date field in this case. Now you can filter your data by either Year or Month, or both. This should be much easier for you that the Grouping method I described earlier.

For pivot tables, it doesn't matter what formulas are used or where the formulas pull the data from. All that matters is what is displayed in the "rectangle" that contains your raw data (A1:E11 in this case).

Another option for you is to use AutoFilters. Select A1:E1, then from the ribbon click Data - Filter. You'll notice small triangles in each header cell that when clicked on will display a drop down list to allow you to filter or sort the data. If you put this formula in cell B12, it will give the total of only the filtered (displayed) rows:

=SUBTOTAL(9,B2:B11)

Here's a link with more detail on autofilters: http://www.contextures.com/xlautofilter01.html