# Need guidance with SUM IF by month

#### TerrSan

##### New Member
Hi,

I have a sheet that records transactions on a daily basis and another that gives a summary. I need a formula that sums values in a column in the transaction sheet based on the month it was recorded. The month needed is from another cell. I did a google search but none of the formulas other people have used is working for me. I have tried:

=SUM(IF(MONTH(Table5[Date])=Reports!C22,Table5[Income],0)) but i get #VALUE! error. i have tried sumif and sumproduct but i always get 0 or the same error.

the dates are in the "Date" column of table 5; the values i need to sum are in the "Income" column of Table 5; and the month i need to filter is in cell C22. What am i doing wrong and what is the solution?

Regards

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### AlphaFrog

##### MrExcel MVP
The MONTH function returns a number 1 to 12. Does C22 have a month number?

#### TerrSan

##### New Member
Yes, in C22 the user selects a number from 1 to 12 from a drop down list. the cell is formatted as general.

#### steve the fish

##### Well-known Member
Are you entering CNTL-SHIFT-ENTER?

#### James006

##### Well-known Member
Code:
``=SUMPRODUCT(--(MONTH(Table5[Date])=Reports!C22),Table5[Income])``

HTH

#### TerrSan

##### New Member
thank you James that works!

does that have anything to do with ctrl-shift-enter? i'll have to read up about what that does...

if i wanted to add more arguments, for example, filter by month AND model no. how would that look?

Last edited:

#### Oeldere

##### Well-known Member
Since you use a table, you can also use a pivot table to analyze your data.

#### James006

##### Well-known Member
thank you James that works!

does that have anything to do with ctrl-shift-enter? i'll have to read up about what that does...

if i wanted to add more arguments, for example, filter by month AND model no. how would that look?

Why is Sumproduct() truly nice :

1. You can add as many conditions as you wish ...
2. You do not have to worry about ctrl-shift-enter ...

Say the model is located in cell C23 :

Code:
``=SUMPRODUCT(--(MONTH(Table5[Date])=Reports!C22)*[FONT=Verdana]--(Table5[Model]=Reports!C23)[/FONT][FONT=Verdana],Table5[Income])[/FONT]</pre>``

HTH

#### TerrSan

##### New Member
Wow thanks James. that's really helpful!

Cheers

Replies
1
Views
666
Replies
6
Views
380
Replies
1
Views
147
Replies
1
Views
167
Replies
0
Views
187

1,191,717
Messages
5,988,259
Members
440,146
Latest member
rgomes8

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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