Sum Product - Months, Year Criteria

[T86157]

I want to sum each month's food costs for a particular year (N4:N15). N4:N15 will be referencing columns A, E and I and summing the costs from columns B, F and J.

Not sure if there is a function or way to translate month abbreviations so that they filter and match the raw data being entered in. For example, Aug = 18-Aug. I was using the MONTH() function but the abbreviation does not convert the month to a number but the raw data date does. Please advise. Do not go by the current formula in N11, I want to add Breakfast, Lunch and Dinner for any given month out of the year.

Regards,
T86157

[Fotis1991]

Convert column M to dates. Now it's General Format and Excel does not "understand" that Aug, is a date..

Type in M11, 01/08/12 and use this Custom Format. ddd. Will show Aug.

Then use this formula in N11.

=SUMPRODUCT((MONTH(E4:E1000)=MONTH(M11))*(YEAR(E4:E1000)=M$3)*(F4:F1000))

Result is 8.41

[T86157]

My excel date format must be different from yours. 01/08/12 gives me January 8, 2012 instead of August 1, 2012. Same with "ddd", that gives me day and not month. Month I figured out for my excel is "mmm" and 08/01/12 gave me August. Not sure why your particular example procedure give me what you got...

Oops! I also forgot to add, for the month. So I want to add up all the food costs for the current month of August which includes the columns of Breakfast, Lunch and Dinner. Not just Lunch. I think the formula is a little more complicated than that?

[Fotis1991]

Apologize for this. Format "mmm", gives you name of the Month.

[T86157]

Fotis,

I want to sum not only Lunch for August but Breakfast and Dinner as well.

[Fotis1991]

Try to add 3 Sumproduct. Something like this.

=SUMPRODUCT((MONTH(E4:E1000)=MONTH(M11))*(YEAR(E4:E1000)=M$3)*(F4:F1000))+SUMPRODUCT((MONTH(A4:A1000)=MONTH(M11))*(YEAR(A4:A1000)=M$3)*(B4:B1000)+SUMPRODUCT((MONTH(I4:I1000)=MONTH(M11))*(YEAR(I4:I1000)=M$3)*(J4:J1000)))

[Kevin UK]

Hi T86157
As Fotis1991, but using the text function. In cell N4 and drag down.

=IFERROR(ROUND(SUMPRODUCT(--(TEXT($A$4:$A$1000,"mmm")=$M4),$B$4:$B$1000)+SUMPRODUCT(--(TEXT($E$4:$E$1000,"mmm")=$M4),$F$4:$F$1000)+SUMPRODUCT(--(TEXT($I$4:$I$1000,"mmm")=$M4),$J$4:$J$1000),2),"")

[T86157]

With either equation, Kevin or Fotis, is there a compact/efficient way of making an IF function so that if the sumproduct is equal to zero, then make the cell blank, otherwise sumproduct. Below is the function:

=if((SUMPRODUCT((MONTH(E4:E1000)=MONTH(M11))*(YEAR(E4:E1000)=M$3)*(F4:F1000))+SUMPRODUCT((MONTH(A4:A1000)=MONTH(M11))*(YEAR(A4:A1000)=M$3)*(B4:B1000))+SUMPRODUCT((MONTH(I4:I1000)=MONTH(M11))*(YEAR(I4:I1000)=M$3)*(J4:J1000)))=0,"",SUMPRODUCT((MONTH(E4:E1000)=MONTH(M11))*(YEAR(E4:E1000)=M$3)*(F4:F1000))+SUMPRODUCT((MONTH(A4:A1000)=MONTH(M11))*(YEAR(A4:A1000)=M$3)*(B4:B1000))+SUMPRODUCT((MONTH(I4:I1000)=MONTH(M11))*(YEAR(I4:I1000)=M$3)*(J4:J1000)))

The redundancy is found in the sumproduct for the if statement and then for the false statement. Any help on making this more compact and efficient would be greatly appreciated. Please advise. Thank you

[Kevin UK]

Hi T86157

Option 1: As the formula I posted in thread #7 and uncheck Show a zero in cells that have a zero value. In File, Options, Advanced.

Option 2: =IF(ROUND(SUMPRODUCT(--(TEXT($A$4:$A$1000,"mmm")=$M4),$B$4:$B$1000)+SUMPRODUCT(--(TEXT($E$4:$E$1000,"mmm")=$M4),$F$4:$F$1000)+SUMPRODUCT(--(TEXT($I$4:$I$1000,"mmm")=$M4),$J$4:$J$1000),2)=0,"",ROUND(SUMPRODUCT(--(TEXT($A$4:$A$1000,"mmm")=$M4),$B$4:$B$1000)+SUMPRODUCT(--(TEXT($E$4:$E$1000,"mmm")=$M4),$F$4:$F$1000)+SUMPRODUCT(--(TEXT($I$4:$I$1000,"mmm")=$M4),$J$4:$J$1000),2))

Mind you someone else should have a shorter idea.