Need guidance with SUM IF by month

[TerrSan]

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

 

[AlphaFrog]

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

 

[TerrSan]

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]

Are you entering CNTL-SHIFT-ENTER?

 

[James006]

What about :

=SUMPRODUCT(--(MONTH(Table5[Date])=Reports!C22),Table5[Income])

HTH

 

[TerrSan]

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?

 

[Oeldere]

Since you use a table, you can also use a pivot table to analyze your data.

 

[James006]

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 :

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

HTH

 

[TerrSan]

Wow thanks James. that's really helpful!

 

[James006]

Glad you could fix your problem

Cheers