Sumif with Index Match w/multiple criteria

[erniepoe]

Hi all,

I've got the following formula combining an index match with a sumif, but it's returning the wrong result. The goal is to sum all the values that equal what is in C8 that match column A on the Niagara Summary page, and then sum the values based on the index match criteria below.

The index match formula is working correctly in that it will return the first value when the sumif portion is removed, so i'm guessing the error lies within the sumif syntax.

Any ideas? Thanks so much for all your help.

Best,
Ernie



=IFERROR(SUMIF('[2019 Forecast - April v2.xlsx]Niagara Summary (CAD)'!$A:$A,$C8,INDEX('[2019 Forecast - April v2.xlsx]Niagara Summary (CAD)'!$C:$GZ,MATCH($C8,'[2019 Forecast - April v2.xlsx]Niagara Summary (CAD)'!$A:$A,0),MATCH(D$3&D$4,'[2019 Forecast - April v2.xlsx]Niagara Summary (CAD)'!$C$4:$GZ$4&'[2019 Forecast - April v2.xlsx]Niagara Summary (CAD)'!$C$5:$GZ$5,0))),0)

 

[ExcelElliott]

Are you looking for something like the following:

Basically, conditionally sum on the Product, but only based on one month?

	A	B	C	D	E	F	G
Product
B
B
A
A
B
B
Product	Month	Total Sales for Month
B	March

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: center"]January[/TD]
[TD="align: center"]February[/TD]
[TD="align: center"]March[/TD]
[TD="align: center"]April[/TD]
[TD="align: center"]May[/TD]
[TD="align: center"]June[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2152[/TD]
[TD="align: right"]1543[/TD]
[TD="align: right"]2390[/TD]
[TD="align: right"]1235[/TD]
[TD="align: right"]2242[/TD]
[TD="align: right"]2535[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]876[/TD]
[TD="align: right"]921[/TD]
[TD="align: right"]798[/TD]
[TD="align: right"]921[/TD]
[TD="align: right"]955[/TD]
[TD="align: right"]1044[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1222[/TD]
[TD="align: right"]890[/TD]
[TD="align: right"]2501[/TD]
[TD="align: right"]932[/TD]
[TD="align: right"]1134[/TD]
[TD="align: right"]2876[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]934[/TD]
[TD="align: right"]1197[/TD]
[TD="align: right"]1443[/TD]
[TD="align: right"]1090[/TD]
[TD="align: right"]1014[/TD]
[TD="align: right"]1389[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1011[/TD]
[TD="align: right"]1290[/TD]
[TD="align: right"]1503[/TD]
[TD="align: right"]967[/TD]
[TD="align: right"]1409[/TD]
[TD="align: right"]1733[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]756[/TD]
[TD="align: right"]992[/TD]
[TD="align: right"]1321[/TD]
[TD="align: right"]871[/TD]
[TD="align: right"]855[/TD]
[TD="align: right"]1477[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]6012[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C12[/TH]
[TD="align: left"]=IFERROR(SUMPRODUCT(($A$3:$A$8=A12)*(INDEX($B$3:$G$8,,MATCH(B12,$B$2:$G$2,0)))),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]