# Need help with SUM functions based on dates and other criterias

#### MrHaugen

##### New Member
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:

 A B C D 1 04.01.2013 340 Transportation All 2 10.02.2014 460 House 3 23.02.2014 200 Food 4 17.03.2013 340 Power 5 24.03.2014 500 House 6 24.03.2014 25 Transportation 7 01.07.2014 123 Food 8 21.08.2013 320 Cloths 9 25.08.2014 105 Transportation 10 28.08.2014 150 Power

<tbody>
</tbody>

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!

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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

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.

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.

---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:

 J K L M 1 year start month end month Expense Type 2 2014 1 12 power

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

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:
Code:
``=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:
Code:
``=SUBTOTAL(9,B2:B11)``
Here's a link with more detail on autofilters: http://www.contextures.com/xlautofilter01.html

Replies
4
Views
229
Replies
8
Views
3K
Replies
6
Views
794

1,202,983
Messages
6,052,903
Members
444,610
Latest member
dodong

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back