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:

	A	B	C	D
1		JAN	FEB	MAR
2	Purchase	100.00	150.00	225.00
3	Insurance	500.00	500.00	500.00
4	Ads	1,000.00	750.00	250.00
5	Ads	1,500.00	500.00	250.00
6	Supply	500.00	425.00	350.00
7	Purchase	250.00	300.00	550.00
8	Insurance	150.00	150.00	75.00
9	Insurance	75.00	75.00	75.00
10	Supply	150.00	250.00	300.00
11	Purchase	125.00	225.00	250.00

<tbody>
</tbody>

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

	A	B	C	D
1		JAN	FEB	MAR
2	Ads	2,500.00
3	Insurance	725.00
4	Purchase	475.00
5	Supply	650.00

<tbody>
</tbody>

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!!

 

[sheetspread]

Re: Help w/ Sumif Array w/ Index

Excel 2010
	A	B	C	D	E	F	G	H	I
1		JAN	FEB	MAR			JAN	FEB	MAR
2	Purchase	100	150	225		Ads	2,500.00	1,250.00	500.00
3	Insurance	500	500	500		Insurance	725.00	725.00	650.00
4	Ads	1,000.00	750	250		Purchase	475.00	675.00	1,025.00
5	Ads	1,500.00	500	250		Supply	650.00	675.00	650.00
6	Supply	500	425	350
7	Purchase	250	300	550
8	Insurance	150	150	75
9	Insurance	75	75	75
10	Supply	150	250	300
11	Purchase	125	225	250
Sheet3

Cell Formulas
Range	Formula
G2	=SUMIF($A$2:$A$11,$F2,B$2:B$11)

 

[DEHA11]

Re: Help w/ Sumif Array w/ Index

Unfortunately, the formula you provided isn't quite what I am looking for. As mentioned, I simplified the "Data" sheet for simplicity, but it's much more complex. I really need a formula that will evaluate the entire "Data" sheet and return the SUM of all cells that match the 'category' in Column A and the 'month' in Row 1.

 

[Aladin Akyurek]

Re: Help w/ Sumif Array w/ Index

Assuming that A2:A5 of Summary is given...

In B2 of Summary enter, copy across, and down:

=SUMIFS(INDEX(Data!$B:$D,0,MATCH(B$1,INDEX(Data!$B:$D,1,0),0)),Data!$A:$A,$A2)

 

[DEHA11]

Re: Help w/ Sumif Array w/ Index

Assuming that A2:A5 of Summary is given...

In B2 of Summary enter, copy across, and down:

=SUMIFS(INDEX(Data!$B:$D,0,MATCH(B$1,INDEX(Data!$B:$D,1,0),0)),Data!$A:$A,$A2)

This is returning a result of 0 which I know is inaccurate...

 

[sheetspread]

Re: Help w/ Sumif Array w/ Index

Both of these work:

Excel 2010
	A	B	C	D	E	F	G	H	I
1		JAN	FEB	MAR			JAN	FEB	MAR
2	Purchase	100	150	225		Ads	2,500.00	1,250.00	500.00
3	Insurance	500	500	500		Insurance	725.00	725.00	650.00
4	Ads	1,000.00	750	250		Purchase	475.00	675.00	1,025.00
5	Ads	1,500.00	500	250		Supply	650.00	675.00	650.00
6	Supply	500	425	350
7	Purchase	250	300	550
8	Insurance	150	150	75
9	Insurance	75	75	75
10	Supply	150	250	300
11	Purchase	125	225	250
Sheet13 (2)

Cell Formulas
Range	Formula
G2	=SUMIF($A$2:$A$11,$F2,INDEX($B$2:$D$11,,MATCH(G$1,$B$1:$D$1,0)))

Excel 2010
	A	B	C	D	E	F	G	H	I
1		JAN	FEB	MAR			JAN	FEB	MAR
2	Purchase	100	150	225		Ads	2,500.00	1,250.00	500.00
3	Insurance	500	500	500		Insurance	725.00	725.00	650.00
4	Ads	1,000.00	750	250		Purchase	475.00	675.00	1,025.00
5	Ads	1,500.00	500	250		Supply	650.00	675.00	650.00
6	Supply	500	425	350
7	Purchase	250	300	550
8	Insurance	150	150	75
9	Insurance	75	75	75
10	Supply	150	250	300
11	Purchase	125	225	250
Sheet13

Cell Formulas
Range	Formula
G2	=SUMPRODUCT(--($A$2:$A$11=$F2)*--($B$1:$D$1=G$1)*$B$2:$D$11)

But if your data structure is different show us where

 

[MARZIOTULLIO]

Re: Help w/ Sumif Array w/ Index


​

	A​	B​	C​	D​	E​	F​
1​		JAN	FEB	MAR		DATA
2​	Purchase	100​	150​	225​
3​	Insurance	500​	500​	500​
4​	Ads	1000​	750​	250​
5​	Ads	1500​	500​	250​
6​	Supply	500​	425​	350​
7​	Purchase	250​	300​	550​
8​	Insurance	150​	150​	75​
9​	Insurance	75​	75​	75​
10​	Supply	150​	250​	300​
11​	Purchase	125​	225​	250​

<tbody>
</tbody>

	A​	B​	C​	D​	E​	F​
1​		JAN	FEB	MAR		Summary
2​	Ads	2500​	1250​	500​
3​	Insurance	725​	725​	650​
4​	Purchase	475​	675​	1025​
5​	Supply	650​	675​	650​

<tbody>
</tbody>

Summary

B2=SUMPRODUCT((Data!$A$2:$A$11=Summary!$A2)*(Data!$B$1:$D$1=Summary!B$1)*(Data!$B$2:$D$11)) copy across and down

 

[DEHA11]

Re: Help w/ Sumif Array w/ Index

B2=SUMPRODUCT((Data!$A$2:$A$11=Summary!$A2)*(Data!$B$1:$D$1=Summary!B$1)*(Data!$B$2:$D$11)) copy across and down

I triple checked that all of the cell references are correct, but this formula is not working either.

 

[DEHA11]

Re: Help w/ Sumif Array w/ Index

YES! This is what I'm looking for!

THANK YOU!!

 

[Aladin Akyurek]

Re: Help w/ Sumif Array w/ Index

This is returning a result of 0 which I know is inaccurate...

Nope. Here is the workbook which implements the set up: https://www.dropbox.com/s/0sqk39b11bml3dd/DEHA11 Help w Sumif Array w Index.xlsx?dl=0