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
2	Product	January	February	March	April	May	June
3	B	2152	1543	2390	1235	2242	2535
4	B	876	921	798	921	955	1044
5	A	1222	890	2501	932	1134	2876
6	A	934	1197	1443	1090	1014	1389
7	B	1011	1290	1503	967	1409	1733
8	B	756	992	1321	871	855	1477
9
10
11	Product	Month	Total Sales for Month
12	B	March	6012

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula C12 =IFERROR(SUMPRODUCT(($A$3:$A$8=A12)*(INDEX($B$3:$G$8,,MATCH(B12,$B$2:$G$2,0)))),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>