SumProduct Formula with Multiple Criteria

[krunk]

I have a spreadsheet with a budget and by month by product. I am trying to use the Sumproduct Formula to calculate the YTD budget based on 2 criteria. I've looked at others formula's and i cannot seem to understand why my formula isn't working.

Here is how i have it laid out:

Column c6:c14 = Product
column d5:05 = Month (January, February, March... )
Begining in column d6:o13 i have the data that i am trying to sum. For instance if June, i want is to sum everything less than June so May - Dec. for a particular product. Here is my formula.

=SUMPRODUCT(('YTD Volume Pivot'!$C$11:$J$11=$C27)*('YTD Volume Pivot'!$A$12:$A$27<$D$5),'YTD Volume Pivot'!$C$12:$J$27)

PLEASE HELP. I am getting desperate.

[EHarvill]

It might be easier to understand what you want and possible solutions if you attach a spreadsheet.
Thanks,
Evelyn

[krunk]

trying to attach...

[NBVC]

Is that the workbook you wanted to attach? I don't see the relation

[krunk]

this is just the summary sheet and the budget sheet is the other tab where i am trying to pull the data. if you look at the ytd columns in I and F 27 it will show you how i am trying to match the aggregator name and Month to the budget sheet to pull in where the column header and row match the criteria. The formula just returns the wrong value and i cannot figure out what i am doing wrong. The actually sheet is much larger and it will not let me attached.

[NBVC]

Without seeing the source workbook, it is not possible to fully diagnose.

Suggestions I can make are make sure that there are no extra spaces in text fields, that spelling matches exactly, and that number fields are actually numbers, and not text that look like numbers and that you have covered the full ranges.... the fact that you are getting back results means, the formula is generally okay.... but that is all I can offer without seeing the source.

[krunk]

test.xlsxHere is the sheet again, i highlighted the cell where i am trying to return the sumproduct using the Forecast tab. Cell G16 to be exact. It is returning 71,702 but it should be returning 66,755.

[NBVC]

It is because you have months as text strings... which you can't use the boolean check accurately against.

I suggest you change the months in the Forecast sheet to the first of each month... then format the cells as Custom with type: mmmm (this will show the month name only).

Then you change formula in G16 to:

=SUMPRODUCT((Forecast!$C$6:$C$13=$D2)*(Forecast!D4:O4<(D5&" 1")+0),Forecast!D6:O13)