Shrink this sumproduct formula

K

Kronos

New Member
Joined
Sep 28, 2011
Messages
2
Hi,

I am brand new to posting on the forum but have hit a brick wall and can't get any futher with this. If I fall down on forum etiquete please put me right.

I need to shorten this formula so that I can ctrl+R the worksheet reference and to neaten it up.

It is designed to sum the visible results in a filtered list on worksheet "rev" where the value in column E on rev matches the value in cell B13 on the front page. It is a long repitition of the same clause as it is summing the values from mutliple columns on worksheet rev.

=SUMPRODUCT(SUBTOTAL(109,OFFSET(rev!$M$3:$M$248,ROW(rev!$M$3:$M$248)-MIN(ROW(rev!$M$3:$M$248)),,1,1))*(rev!$E$3:$E$248=B13))+SUMPRODUCT(SUBTOTAL(109,OFFSET(rev!$P$3:$P$248,ROW(rev!$P$3:$P$248)-MIN(ROW(rev!$P$3:$P$248)),,1,1))*(rev!$E$3:$E$248=B13))+SUMPRODUCT(SUBTOTAL(109,OFFSET(rev!$S$3:$S$248,ROW(rev!$S$3:$S$248)-MIN(ROW(rev!$S$3:$S$248)),,1,1))*(rev!$E$3:$E$248=B13))+SUMPRODUCT(SUBTOTAL(109,OFFSET(rev!$V$3:$V$248,ROW(rev!$V$3:$V$248)-MIN(ROW(rev!$V$3:$V$248)),,1,1))*(rev!$E$3:$E$248=B13))+SUMPRODUCT(SUBTOTAL(109,OFFSET(rev!$Y$3:$Y$248,ROW(rev!$Y$3:$Y$248)-MIN(ROW(rev!$Y$3:$Y$248)),,1,1))*(rev!$E$3:$E$248=B13))+SUMPRODUCT(SUBTOTAL(109,OFFSET(rev!$AB$3:$AB$248,ROW(rev!$AB$3:$AB$248)-MIN(ROW(rev!$AB$3:$AB$248)),,1,1))*(rev!$E$3:$E$248=B13))+SUMPRODUCT(SUBTOTAL(109,OFFSET(rev!$AE$3:$AE$248,ROW(rev!$AE$3:$AE$248)-MIN(ROW(rev!$AE$3:$AE$248)),,1,1))*(rev!$E$3:$E$248=B13))+SUMPRODUCT(SUBTOTAL(109,OFFSET(rev!$AH$3:$AH$248,ROW(rev!$AH$3:$AH$248)-MIN(ROW(rev!$AH$3:$AH$248)),,1,1))*(rev!$E$3:$E$248=B13))

This is the function I used previously however I am attempting to add the subtotal function to account for a filtered list.

=SUMPRODUCT(--(wk26!$E$3:$E$249=B13),wk26!$N$3:$N$249+wk26!$Q$3:$Q$249+wk26!$T$3:$T$249+wk26!$W$3:$W$249+wk26!$Z$3:$Z$249+wk26!$AC$3:$AC$249+wk26!$AF$3:$AF$249+wk26!$AI$3:$AI$249)

Any help you could offer would be greatly appreciated. In return I'm off to see if there is anyone I could help out with their issue.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
Maybe this:

Note: you have two formulas - Array and Help Tab, choose one.

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH><TH>L</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Help</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">M1</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">P1</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">S1</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">V1</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Y1</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">AB1</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">AE1</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">AH1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Tab</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">0</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">3</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">6</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">9</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">12</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">15</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">18</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">21</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1; FONT-WEIGHT: bold">Result</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1; FONT-WEIGHT: bold">Result</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: center">A</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1920</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1920</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="TEXT-ALIGN: center">******</TD><TD style="TEXT-ALIGN: center">******</TD><TD style="TEXT-ALIGN: center">******</TD><TD style="TEXT-ALIGN: center">******</TD><TD style="TEXT-ALIGN: center">******</TD><TD style="TEXT-ALIGN: center">******</TD><TD style="TEXT-ALIGN: center">******</TD><TD style="TEXT-ALIGN: center">******</TD><TD style="TEXT-ALIGN: center">******</TD><TD style="TEXT-ALIGN: center">******</TD><TD style="TEXT-ALIGN: center">******</TD></TR></TBODY></TABLE>Master


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E10</TH><TD style="TEXT-ALIGN: left">=COLUMN(INDIRECT(E9))-COLUMN(INDIRECT($E$9))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C13</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUBTOTAL(109,OFFSET(rev!M3:M20,ROW(rev!M3:M20)-MIN(ROW(rev!M3:M20)),{0,3,6,9,12,15,18,21},1,1))*(rev!E3:E20=B13))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D13</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUBTOTAL(109,OFFSET(rev!M3:M20,ROW(rev!M3:M20)-MIN(ROW(rev!M3:M20)),E10:L10,1,1))*(rev!E3:E20=B13))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Markmzz
 
Upvote 0
If it makes it easier, your original formula can be adjusted to

=SUMPRODUCT(($E$3:$E$249=B13)*(MOD(COLUMN($N$3:$AI$249),3)=2)*$N$3:$AI$249)


Now, using subtotal for filtered ranges is not my forte..
But It looks like you already know how to do it...

You don't need to add sheet reference or the subotal bit to this part
COLUMN($N$3:$AI$249)


Hope that helps..
 
Upvote 0
Very nice!

But I think you have a typo in the data range -- you are offsetting by one row and one column. Or am I missing something in the logic?
 
Upvote 0
Do you have anything in a header row to differentiate the columns you are summing? That would make things a little simpler perhaps, e.g. if all of those columns have "Total" in row 2 then try this version

=SUMPRODUCT(SUBTOTAL(2,OFFSET(rev!$M$3:$M$248,ROW(rev!$M$3:$M$248)-MIN(ROW(rev!$M$3:$M$248)),0,1))*(rev!$E$3:$E$248=B13)*(rev!$M$2:$AH$2="Total"),rev!$M$3:$AH$248)
 
Upvote 0
Jonmo1,

If I'm not wrong, maybe this array formula:

=SUM(($E$3:$E$20=E1)*IF(ISNUMBER($M$3:$AH$20),(MOD(COLUMN($M$3:$AH$20),3)=1)*$M$3:$AH$20))

Markmzz
 
Upvote 0
markmzz said:
Jonmo1,

If I'm not wrong, maybe this array formula:

=SUM(($E$3:$E$20=E1)*IF(ISNUMBER($M$3:$AH$20),(MOD(COLUMN($M$3:$AH$20),3)=1)*$M$3:$AH$20))

Markmzz
Click to expand...
I can see how that works (for a subset of the range) to ignore non-numerical values, and it does give the same result.

So it would seem that all you would have to do is change "SUM(" to "SUBTOTAL(109," and enter as an array formula, but that doesn't seem to work. Any ideas why (not)?
 
Upvote 0
SUBTOTAL expects a range or ranges - it won't accept an array in normal circumstances - that's why you need the SUBTOTAL(109,OFFSET(.... type setup
from the original formula or my suggested version.

You can actually ignore non-numerics with this version

=SUMPRODUCT(($E$3:$E$20=E1)*(MOD(COLUMN($M$3:$AH$20)-COLUMN($M$3),3)=0),$M$3:$AH$20)

and then you need the extra criterion to include visible row only

=SUMPRODUCT(SUBTOTAL(2,OFFSET($M$3:$M$20,ROW($M$3:$M$20)-MIN(ROW($M$3:$M$20)),0,1))*($E$3:$E$20=E1)*(MOD(COLUMN($M$3:$AH$20)-COLUMN($M$3),3)=0),$M$3:$AH$20)
 
Upvote 0
You must log in or register to reply here.

Similar threads

gtd526
  • Solved
vba selecting entire range from offset
Replies
5
Views
358
Tetra201
Tetra201
gtd526
  • Question Question
vba Sorting Columns w/ 2 Criteria's
Replies
1
Views
331
gtd526
gtd526
gtd526
  • Solved
Move selected row to top of range
Replies
6
Views
367
gtd526
gtd526
gtd526
  • Question Question
vba selecting a specific range if ISNUMBER
Replies
1
Views
364
gtd526
gtd526
J
  • Solved
VBA Code To Loop Through One Worksheet and Paste Certain Values Into Another
Replies
5
Views
165
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,558
Messages
6,179,512
Members
452,921
Latest member
BBQKING

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