Sumif Array with Index

[DEHA11]

I have two sheets in the same workbook. One has "Data" and the other is a "Summary".

For the sake of simplicity, lets assume the "Data" sheet looks like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]JAN[/TD]
[TD="align: center"]FEB[/TD]
[TD="align: center"]MAR[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Purchase[/TD]
[TD="align: center"]100.00[/TD]
[TD="align: center"]150.00[/TD]
[TD="align: center"]225.00[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Insurance[/TD]
[TD="align: center"]500.00[/TD]
[TD="align: center"]500.00[/TD]
[TD="align: center"]500.00[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Ads[/TD]
[TD="align: center"]1,000.00[/TD]
[TD="align: center"]750.00[/TD]
[TD="align: center"]250.00[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Ads[/TD]
[TD="align: center"]1,500.00[/TD]
[TD="align: center"]500.00[/TD]
[TD="align: center"]250.00[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Supply[/TD]
[TD="align: center"]500.00[/TD]
[TD="align: center"]425.00[/TD]
[TD="align: center"]350.00[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Purchase[/TD]
[TD="align: center"]250.00[/TD]
[TD="align: center"]300.00[/TD]
[TD="align: center"]550.00[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]Insurance[/TD]
[TD="align: center"]150.00[/TD]
[TD="align: center"]150.00[/TD]
[TD="align: center"]75.00[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]Insurance[/TD]
[TD="align: center"]75.00[/TD]
[TD="align: center"]75.00[/TD]
[TD="align: center"]75.00[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]Supply[/TD]
[TD="align: center"]150.00[/TD]
[TD="align: center"]250.00[/TD]
[TD="align: center"]300.00[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]Purchase[/TD]
[TD="align: center"]125.00[/TD]
[TD="align: center"]225.00[/TD]
[TD="align: center"]250.00[/TD]
[/TR]
</tbody>[/TABLE]

I'm trying to get the "Summary" sheet to update with the corresponding summed values for each month, like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]JAN[/TD]
[TD="align: center"]FEB[/TD]
[TD="align: center"]MAR[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Ads[/TD]
[TD="align: center"]2,500.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Insurance[/TD]
[TD="align: center"]725.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Purchase[/TD]
[TD="align: center"]475.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Supply[/TD]
[TD="align: center"]650.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

I started with =INDEX(Data!A1:D11,MATCH('Summary'!A2,Data!A2:A11,0),Match('Summary'!B1,Data!B1:D1,0))

but, of course, this only returns the first value with the matching data rather than summing all of the "matching" entries.

I changed my formula to a =SUM(IF(.... {ARRAY} and I tried a =SUM(INDEX(... formula, but neither are returning the intended result, if any.


I'd really appreciate assistance with this.

TY!!

 

[mikerickson]

Re: Help w/ Sumif Array w/ Index

Have you looked at Pivot Tables?

 

[DEHA11]

Re: Help w/ Sumif Array w/ Index

I was able to get this to work correctly. I must have had a cell referenced incorrectly which resulted in the "0" indicating it was finding the intended data.

My formula "=SUMIFS(INDEX(Data!$G:$AN,0,MATCH(J$3,INDEX(Data!$G:$AN,1,0),0)),Data!$A:$A,$B17)"

Thanks for the example, it helped a great deal!

 

[Aladin Akyurek]

Re: Help w/ Sumif Array w/ Index

I was able to get this to work correctly. I must have had a cell referenced incorrectly which resulted in the "0" indicating it was finding the intended data.

My formula "=SUMIFS(INDEX(Data!$G:$AN,0,MATCH(J$3,INDEX(Data!$G:$AN,1,0),0)),Data!$A:$A,$B17)"

Thanks for the example, it helped a great deal!

Glad to help.