Sumif Array with Index

D

DEHA11

New Member
Joined
Jun 25, 2018
Messages
15
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!!
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Re: Help w/ Sumif Array w/ Index


Excel 2010
ABCDEFGHI
1JANFEBMARJANFEBMAR
2Purchase100150225Ads2,500.001,250.00500.00
3Insurance500500500Insurance725.00725.00650.00
4Ads1,000.00750250Purchase475.00675.001,025.00
5Ads1,500.00500250Supply650.00675.00650.00
6Supply500425350
7Purchase250300550
8Insurance15015075
9Insurance757575
10Supply150250300
11Purchase125225250
Sheet3
Cell Formulas
RangeFormula
G2=SUMIF($A$2:$A$11,$F2,B$2:B$11)
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
Re: Help w/ Sumif Array w/ Index

Aladin Akyurek said:
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)
Click to expand...


This is returning a result of 0 which I know is inaccurate... :(
 
Upvote 0
Re: Help w/ Sumif Array w/ Index

Both of these work:


Excel 2010
ABCDEFGHI
1JANFEBMARJANFEBMAR
2Purchase100150225Ads2,500.001,250.00500.00
3Insurance500500500Insurance725.00725.00650.00
4Ads1,000.00750250Purchase475.00675.001,025.00
5Ads1,500.00500250Supply650.00675.00650.00
6Supply500425350
7Purchase250300550
8Insurance15015075
9Insurance757575
10Supply150250300
11Purchase125225250
Sheet13 (2)
Cell Formulas
RangeFormula
G2=SUMIF($A$2:$A$11,$F2,INDEX($B$2:$D$11,,MATCH(G$1,$B$1:$D$1,0)))



Excel 2010
ABCDEFGHI
1JANFEBMARJANFEBMAR
2Purchase100150225Ads2,500.001,250.00500.00
3Insurance500500500Insurance725.00725.00650.00
4Ads1,000.00750250Purchase475.00675.001,025.00
5Ads1,500.00500250Supply650.00675.00650.00
6Supply500425350
7Purchase250300550
8Insurance15015075
9Insurance757575
10Supply150250300
11Purchase125225250
Sheet13
Cell Formulas
RangeFormula
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
 
Upvote 0
Re: Help w/ Sumif Array w/ Index


​[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[TD]
F​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD][/TD]
[TD]JAN[/TD]
[TD]FEB[/TD]
[TD]MAR[/TD]
[TD][/TD]
[TD]DATA[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]Purchase[/TD]
[TD]
100​
[/TD]
[TD]
150​
[/TD]
[TD]
225​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]Insurance[/TD]
[TD]
500​
[/TD]
[TD]
500​
[/TD]
[TD]
500​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]Ads[/TD]
[TD]
1000​
[/TD]
[TD]
750​
[/TD]
[TD]
250​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]Ads[/TD]
[TD]
1500​
[/TD]
[TD]
500​
[/TD]
[TD]
250​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]Supply[/TD]
[TD]
500​
[/TD]
[TD]
425​
[/TD]
[TD]
350​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD]Purchase[/TD]
[TD]
250​
[/TD]
[TD]
300​
[/TD]
[TD]
550​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
8​
[/TD]
[TD]Insurance[/TD]
[TD]
150​
[/TD]
[TD]
150​
[/TD]
[TD]
75​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
9​
[/TD]
[TD]Insurance[/TD]
[TD]
75​
[/TD]
[TD]
75​
[/TD]
[TD]
75​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
10​
[/TD]
[TD]Supply[/TD]
[TD]
150​
[/TD]
[TD]
250​
[/TD]
[TD]
300​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
11​
[/TD]
[TD]Purchase[/TD]
[TD]
125​
[/TD]
[TD]
225​
[/TD]
[TD]
250​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[TD]
F​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD][/TD]
[TD]JAN[/TD]
[TD]FEB[/TD]
[TD]MAR[/TD]
[TD][/TD]
[TD]Summary[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]Ads[/TD]
[TD]
2500​
[/TD]
[TD]
1250​
[/TD]
[TD]
500​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]Insurance[/TD]
[TD]
725​
[/TD]
[TD]
725​
[/TD]
[TD]
650​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]Purchase[/TD]
[TD]
475​
[/TD]
[TD]
675​
[/TD]
[TD]
1025​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]Supply[/TD]
[TD]
650​
[/TD]
[TD]
675​
[/TD]
[TD]
650​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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
 
Upvote 0
Re: Help w/ Sumif Array w/ Index

MARZIOTULLIO said:


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
Click to expand...


I triple checked that all of the cell references are correct, but this formula is not working either.
 
Upvote 0
You must log in or register to reply here.

Similar threads

WESTERNWALL
  • Question Question
Sum and Count functions
Replies
4
Views
241
WESTERNWALL
WESTERNWALL
Craig Peter DG
  • Question Question
SUM OF DAILY TRANSACTION VALUES INTO MONTHLY SUMMARY - MULTIPLE CRITERIA
Replies
3
Views
335
Georgiboy
Georgiboy
L
  • Question Question
Index match working but not all information being brought in
Replies
3
Views
517
Peter_SSs
Peter_SSs
Craig Peter DG
  • Solved
Simple Problem, Tricky Solution
2
Replies
16
Views
799
Georgiboy
Georgiboy
W
  • Solved
Filter/Matching HELP
Replies
5
Views
456
Alex Blakenburg
A

Forum statistics

Threads
1,224,747
Messages
6,180,710
Members
452,994
Latest member
Janick

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top