Sumifs on dynamic rows / columns

D

dmessmer

New Member
Joined
Mar 27, 2017
Messages
6
Hello, I'm trying to do either a sumifs or index(match() in order to sum a range of cells (multiple columns and rows) based upon a reference cell which can be modified to adjust the number of columns which are summed. For example, in the below table on the right are my results. Since A1 is 3 months, the fruit totals are summing columns B,C, & D so long as it's the appropriate type of fruit.

[TABLE="width: 500"]
<tbody>[TR]
[TD]3[/TD]
[TD]Months[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fruit
[/TD]
[TD]1/1/2017
[/TD]
[TD]2/1/2017
[/TD]
[TD]3/1/2017
[/TD]
[TD]4/1/2017[/TD]
[TD][/TD]
[TD]Fruit
[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]10[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]Apple[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]3[/TD]
[TD]8[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]Banana[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]Orange[/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


If my variable was 4 months the results would look like:
[TABLE="width: 500"]
<tbody>[TR]
[TD]4[/TD]
[TD]Months[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fruit
[/TD]
[TD]1/1/2017
[/TD]
[TD]2/1/2017
[/TD]
[TD]3/1/2017
[/TD]
[TD]4/1/2017[/TD]
[TD][/TD]
[TD]Fruit
[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]10[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]Apple[/TD]
[TD]27[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]3[/TD]
[TD]8[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]Banana[/TD]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]Orange[/TD]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any suggestions on how to sum up the totals of ranges based upon a reference cells which makes your range dynamic? Thanks for the help!
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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]
[TD]
G​
[/TD]
[TD]
H​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD]
3​
[/TD]
[TD]Months[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]Fruit[/TD]
[TD]
1/1/2017​
[/TD]
[TD]
2/1/2017​
[/TD]
[TD]
3/1/2017​
[/TD]
[TD]
4/1/2017​
[/TD]
[TD][/TD]
[TD]Fruit[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]Apple[/TD]
[TD]
10​
[/TD]
[TD]
4​
[/TD]
[TD]
6​
[/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD]Apple[/TD]
[TD]
20​
[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]Banana[/TD]
[TD]
3​
[/TD]
[TD]
8​
[/TD]
[TD]
1​
[/TD]
[TD]
5​
[/TD]
[TD][/TD]
[TD]Banana[/TD]
[TD]
12​
[/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]Orange[/TD]
[TD]
2​
[/TD]
[TD]
5​
[/TD]
[TD]
7​
[/TD]
[TD]
3​
[/TD]
[TD][/TD]
[TD]Orange[/TD]
[TD]
14​
[/TD]
[/TR]
</tbody>[/TABLE]


​
H4=SUMPRODUCT((MONTH($B$3:$E$3)<=$A$1)*($A$4:$A$6=$G4)*($B$4:$E$6)) copy down

for months 4 change 3 to 4 in a1

​
 
Last edited:
Upvote 0
Just an option. Needed to be pasted H4

Code: 
=+SUMPRODUCT((G4=$A$4:$A$6)*INDEX($B$4;;):OFFSET($B$4;2;$A$1))


dmessmer said:
Hello, I'm trying to do either a sumifs or index(match() in order to sum a range of cells (multiple columns and rows) based upon a reference cell which can be modified to adjust the number of columns which are summed. For example, in the below table on the right are my results. Since A1 is 3 months, the fruit totals are summing columns B,C, & D so long as it's the appropriate type of fruit.

[TABLE="width: 500"]
<tbody>[TR]
[TD]3[/TD]
[TD]Months[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fruit
[/TD]
[TD]1/1/2017
[/TD]
[TD]2/1/2017
[/TD]
[TD]3/1/2017
[/TD]
[TD]4/1/2017[/TD]
[TD][/TD]
[TD]Fruit
[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]10[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]Apple[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]3[/TD]
[TD]8[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]Banana[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]Orange[/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


If my variable was 4 months the results would look like:
[TABLE="width: 500"]
<tbody>[TR]
[TD]4[/TD]
[TD]Months[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fruit
[/TD]
[TD]1/1/2017
[/TD]
[TD]2/1/2017
[/TD]
[TD]3/1/2017
[/TD]
[TD]4/1/2017[/TD]
[TD][/TD]
[TD]Fruit
[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]10[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]Apple[/TD]
[TD]27[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]3[/TD]
[TD]8[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]Banana[/TD]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]Orange[/TD]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any suggestions on how to sum up the totals of ranges based upon a reference cells which makes your range dynamic? Thanks for the help!
Click to expand...
 
Last edited:
Upvote 0
Thanks for the quick reply, Marziotullio. I thought that by posting a pretty simple example I'd be able to use the help & apply it to my bit more complex problem. Unfortunately I am unable to do so. Let me elaborate.

My scenario is:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]Months[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Results[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Fruit[/TD]
[TD]1/2017[/TD]
[TD]2/2017[/TD]
[TD]3/2017[/TD]
[TD]4/2017[/TD]
[TD]5/2017[/TD]
[TD]6/2017[/TD]
[TD]7/2017[/TD]
[TD]8/2017[/TD]
[TD]9/2017[/TD]
[TD]10/2017[/TD]
[TD]11/2017[/TD]
[TD]12/2017[/TD]
[TD]1/2018[/TD]
[TD]2/2018[/TD]
[TD]3/2018[/TD]
[TD][/TD]
[TD]1/2017[/TD]
[TD]2/2017[/TD]
[TD]3/2017[/TD]
[TD]4/2017[/TD]
[TD]5/2017[/TD]
[TD]6/2017[/TD]
[TD]Etc.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Apple[/TD]
[TD]10[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]3[/TD]
[TD]7[/TD]
[TD]11[/TD]
[TD]14[/TD]
[TD]12[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]9[/TD]
[TD]2[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]20[/TD]
[TD]17[/TD]
[TD]21[/TD]
[TD]18[/TD]
[TD]18[/TD]
[TD]21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Banana[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]11[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]10[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]23[/TD]
[TD]18[/TD]
[TD]20[/TD]
[TD]18[/TD]
[TD]22[/TD]
[TD]21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Orange[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]10[/TD]
[TD]7[/TD]
[TD]10[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]15[/TD]
[TD]20[/TD]
[TD]24[/TD]
[TD]23[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I think that the original solution you've provided my be stopped by the fact that I have multiple years and therefore many of the same month. Additionally, in my results I need it to reference the month in it's own column. So for example, Q3 will add 2 additional months to 1/2017 and therefore will sum(A3,B3,C3). Then R3 would sum(B3,C3,D3), etc.

Can you original solution be modified to accommodate this?
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
How to SUMIF based on criteria from another sheet?
Replies
6
Views
384
Cubist
Cubist
M
  • Question Question
Getting Cell Values Changing Frequently and Rearranging it in a Compact Manner
Replies
7
Views
389
maeyks
M
S
  • Solved
Sum from a table if the key matches a variable array
Replies
3
Views
212
Fluff
Fluff
A
  • Solved
Count multiples rows and ans in a single row and column
2
Replies
13
Views
148
Peter_SSs
Peter_SSs
E
  • Solved
COUNTIF and SUMIF formula with multiple criteria
Replies
2
Views
722
excelnewb221
E

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

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