Summing a group?

C

_CROW

New Member
Joined
Jun 20, 2013
Messages
21
I feel like there is a real simple solution to this, but I cannot for the life of me come up with something simpler...

I have a table that shows customer demand by part number over a number of weeks. I breakdown the full part number into a "base part number", which effectively removes which color the part is. I have several "Common Process Groups" that contain base part numbers. Base part numbers can appear in multiple CPG's. I'd like to sum the customer demand by CPG, by week. The formula i'm using seems a bit archaic.

The customer demand is found on the "830" tab and a breakdown of the CPG's is on the CPG tab.

Using Windows 10 and Excel 2016 64-bit

Here is the portion of the 830 table

BASEPNCustomer Part#WK1WK2WK3WK4WK5WK6WK7WK8WK9WK10WK11
6AD666AD66DX8AH29641955932283122222739
6AD666AD66GW7AH3032885530273118213240
6AD666AD66JSCAH81336221516139101625
6AD666AD66LAUAH176354329171614881320
6AD666AD66MBJAH192762249861578
6AD666AD66MRCAH171949261515149111420
6AD666AD66PDNAH223072361413115101315
6AD666AD66RUAAH193783311111846912
6AD666AD66SE4AH1326334
6AD666AD66VGVAH23527739171713571012
6AD676AD67DX8AH29641955932283122222739
6AD676AD67GW7AH3032885530273118213240
6AD676AD67JSCAH81336221516139101625
6AD676AD67LAUAH176354329171614881320
6AD676AD67MBJAH192762249861578
6AD676AD67MRCAH171949261515149111420
6AD676AD67PDNAH223072361413115101315
6AD676AD67RUAAH193783311111846912


And a portion of the CPG tab

JL 830 REPORT 2.xlsx
DEFGHIJ
1TYPERRLHRRLHRRLHRRLHRRLHRRLH
2CPGCP00026727CP00026728CP00026729CP00026730CP00025534CP00025535
3MAX782445901200459017721274
46AD796CE796CE796SW116QD816JX39
56SW116CE876CE876TY956QB156JX41
66EK156EK156SW09
76AD876AD87
86ZC516AD79
96TY956AD75
106AD75
116SW09
12
13
14
15
16
17
18
20WK20000794648
21WK326294043141401388636
22WK424943913141901340663
23WK520093334132501333669
24WK620663392132601290686
25WK72131354614260866965
26WK81412264912630359724
27WK91969321912750561596
28WK102103339413190947654
29WK11240336811375551039862
CPG
Cell Formulas
RangeFormula
E20:J20E20=SUMIFS(RELEASE[WK2],RELEASE[BASEPN],E$4)+SUMIFS(RELEASE[WK2],RELEASE[BASEPN],E$5)+SUMIFS(RELEASE[WK2],RELEASE[BASEPN],E$6)+SUMIFS(RELEASE[WK2],RELEASE[BASEPN],E$7)+SUMIFS(RELEASE[WK2],RELEASE[BASEPN],E$8)+SUMIFS(RELEASE[WK2],RELEASE[BASEPN],E$9)+SUMIFS(RELEASE[WK2],RELEASE[BASEPN],E$10)+SUMIFS(RELEASE[WK2],RELEASE[BASEPN],E$11)+SUMIFS(RELEASE[WK2],RELEASE[BASEPN],E$12)+SUMIFS(RELEASE[WK2],RELEASE[BASEPN],E$13)+SUMIFS(RELEASE[WK2],RELEASE[BASEPN],E$14)+SUMIFS(RELEASE[WK2],RELEASE[BASEPN],E$15)+SUMIFS(RELEASE[WK2],RELEASE[BASEPN],E$16)
E21:J21E21=SUMIFS(RELEASE[WK3],RELEASE[BASEPN],E$4)+SUMIFS(RELEASE[WK3],RELEASE[BASEPN],E$5)+SUMIFS(RELEASE[WK3],RELEASE[BASEPN],E$6)+SUMIFS(RELEASE[WK3],RELEASE[BASEPN],E$7)+SUMIFS(RELEASE[WK3],RELEASE[BASEPN],E$8)+SUMIFS(RELEASE[WK3],RELEASE[BASEPN],E$9)+SUMIFS(RELEASE[WK3],RELEASE[BASEPN],E$10)+SUMIFS(RELEASE[WK3],RELEASE[BASEPN],E$11)+SUMIFS(RELEASE[WK3],RELEASE[BASEPN],E$12)+SUMIFS(RELEASE[WK3],RELEASE[BASEPN],E$13)+SUMIFS(RELEASE[WK3],RELEASE[BASEPN],E$14)+SUMIFS(RELEASE[WK3],RELEASE[BASEPN],E$15)+SUMIFS(RELEASE[WK3],RELEASE[BASEPN],E$16)
E22:J22E22=SUMIFS(RELEASE[WK4],RELEASE[BASEPN],E$4)+SUMIFS(RELEASE[WK4],RELEASE[BASEPN],E$5)+SUMIFS(RELEASE[WK4],RELEASE[BASEPN],E$6)+SUMIFS(RELEASE[WK4],RELEASE[BASEPN],E$7)+SUMIFS(RELEASE[WK4],RELEASE[BASEPN],E$8)+SUMIFS(RELEASE[WK4],RELEASE[BASEPN],E$9)+SUMIFS(RELEASE[WK4],RELEASE[BASEPN],E$10)+SUMIFS(RELEASE[WK4],RELEASE[BASEPN],E$11)+SUMIFS(RELEASE[WK4],RELEASE[BASEPN],E$12)+SUMIFS(RELEASE[WK4],RELEASE[BASEPN],E$13)+SUMIFS(RELEASE[WK4],RELEASE[BASEPN],E$14)+SUMIFS(RELEASE[WK4],RELEASE[BASEPN],E$15)+SUMIFS(RELEASE[WK4],RELEASE[BASEPN],E$16)
E23:J23E23=SUMIFS(RELEASE[WK5],RELEASE[BASEPN],E$4)+SUMIFS(RELEASE[WK5],RELEASE[BASEPN],E$5)+SUMIFS(RELEASE[WK5],RELEASE[BASEPN],E$6)+SUMIFS(RELEASE[WK5],RELEASE[BASEPN],E$7)+SUMIFS(RELEASE[WK5],RELEASE[BASEPN],E$8)+SUMIFS(RELEASE[WK5],RELEASE[BASEPN],E$9)+SUMIFS(RELEASE[WK5],RELEASE[BASEPN],E$10)+SUMIFS(RELEASE[WK5],RELEASE[BASEPN],E$11)+SUMIFS(RELEASE[WK5],RELEASE[BASEPN],E$12)+SUMIFS(RELEASE[WK5],RELEASE[BASEPN],E$13)+SUMIFS(RELEASE[WK5],RELEASE[BASEPN],E$14)+SUMIFS(RELEASE[WK5],RELEASE[BASEPN],E$15)+SUMIFS(RELEASE[WK5],RELEASE[BASEPN],E$16)
E24:J24E24=SUMIFS(RELEASE[WK6],RELEASE[BASEPN],E$4)+SUMIFS(RELEASE[WK6],RELEASE[BASEPN],E$5)+SUMIFS(RELEASE[WK6],RELEASE[BASEPN],E$6)+SUMIFS(RELEASE[WK6],RELEASE[BASEPN],E$7)+SUMIFS(RELEASE[WK6],RELEASE[BASEPN],E$8)+SUMIFS(RELEASE[WK6],RELEASE[BASEPN],E$9)+SUMIFS(RELEASE[WK6],RELEASE[BASEPN],E$10)+SUMIFS(RELEASE[WK6],RELEASE[BASEPN],E$11)+SUMIFS(RELEASE[WK6],RELEASE[BASEPN],E$12)+SUMIFS(RELEASE[WK6],RELEASE[BASEPN],E$13)+SUMIFS(RELEASE[WK6],RELEASE[BASEPN],E$14)+SUMIFS(RELEASE[WK6],RELEASE[BASEPN],E$15)+SUMIFS(RELEASE[WK6],RELEASE[BASEPN],E$16)
E25:J25E25=SUMIFS(RELEASE[WK7],RELEASE[BASEPN],E$4)+SUMIFS(RELEASE[WK7],RELEASE[BASEPN],E$5)+SUMIFS(RELEASE[WK7],RELEASE[BASEPN],E$6)+SUMIFS(RELEASE[WK7],RELEASE[BASEPN],E$7)+SUMIFS(RELEASE[WK7],RELEASE[BASEPN],E$8)+SUMIFS(RELEASE[WK7],RELEASE[BASEPN],E$9)+SUMIFS(RELEASE[WK7],RELEASE[BASEPN],E$10)+SUMIFS(RELEASE[WK7],RELEASE[BASEPN],E$11)+SUMIFS(RELEASE[WK7],RELEASE[BASEPN],E$12)+SUMIFS(RELEASE[WK7],RELEASE[BASEPN],E$13)+SUMIFS(RELEASE[WK7],RELEASE[BASEPN],E$14)+SUMIFS(RELEASE[WK7],RELEASE[BASEPN],E$15)+SUMIFS(RELEASE[WK7],RELEASE[BASEPN],E$16)
E26:J26E26=SUMIFS(RELEASE[WK8],RELEASE[BASEPN],E$4)+SUMIFS(RELEASE[WK8],RELEASE[BASEPN],E$5)+SUMIFS(RELEASE[WK8],RELEASE[BASEPN],E$6)+SUMIFS(RELEASE[WK8],RELEASE[BASEPN],E$7)+SUMIFS(RELEASE[WK8],RELEASE[BASEPN],E$8)+SUMIFS(RELEASE[WK8],RELEASE[BASEPN],E$9)+SUMIFS(RELEASE[WK8],RELEASE[BASEPN],E$10)+SUMIFS(RELEASE[WK8],RELEASE[BASEPN],E$11)+SUMIFS(RELEASE[WK8],RELEASE[BASEPN],E$12)+SUMIFS(RELEASE[WK8],RELEASE[BASEPN],E$13)+SUMIFS(RELEASE[WK8],RELEASE[BASEPN],E$14)+SUMIFS(RELEASE[WK8],RELEASE[BASEPN],E$15)+SUMIFS(RELEASE[WK8],RELEASE[BASEPN],E$16)
E27:J27E27=SUMIFS(RELEASE[WK9],RELEASE[BASEPN],E$4)+SUMIFS(RELEASE[WK9],RELEASE[BASEPN],E$5)+SUMIFS(RELEASE[WK9],RELEASE[BASEPN],E$6)+SUMIFS(RELEASE[WK9],RELEASE[BASEPN],E$7)+SUMIFS(RELEASE[WK9],RELEASE[BASEPN],E$8)+SUMIFS(RELEASE[WK9],RELEASE[BASEPN],E$9)+SUMIFS(RELEASE[WK9],RELEASE[BASEPN],E$10)+SUMIFS(RELEASE[WK9],RELEASE[BASEPN],E$11)+SUMIFS(RELEASE[WK9],RELEASE[BASEPN],E$12)+SUMIFS(RELEASE[WK9],RELEASE[BASEPN],E$13)+SUMIFS(RELEASE[WK9],RELEASE[BASEPN],E$14)+SUMIFS(RELEASE[WK9],RELEASE[BASEPN],E$15)+SUMIFS(RELEASE[WK9],RELEASE[BASEPN],E$16)
E28:J28E28=SUMIFS(RELEASE[WK10],RELEASE[BASEPN],E$4)+SUMIFS(RELEASE[WK10],RELEASE[BASEPN],E$5)+SUMIFS(RELEASE[WK10],RELEASE[BASEPN],E$6)+SUMIFS(RELEASE[WK10],RELEASE[BASEPN],E$7)+SUMIFS(RELEASE[WK10],RELEASE[BASEPN],E$8)+SUMIFS(RELEASE[WK10],RELEASE[BASEPN],E$9)+SUMIFS(RELEASE[WK10],RELEASE[BASEPN],E$10)+SUMIFS(RELEASE[WK10],RELEASE[BASEPN],E$11)+SUMIFS(RELEASE[WK10],RELEASE[BASEPN],E$12)+SUMIFS(RELEASE[WK10],RELEASE[BASEPN],E$13)+SUMIFS(RELEASE[WK10],RELEASE[BASEPN],E$14)+SUMIFS(RELEASE[WK10],RELEASE[BASEPN],E$15)+SUMIFS(RELEASE[WK10],RELEASE[BASEPN],E$16)
E29:J29E29=SUMIFS(RELEASE[WK11],RELEASE[BASEPN],E$4)+SUMIFS(RELEASE[WK11],RELEASE[BASEPN],E$5)+SUMIFS(RELEASE[WK11],RELEASE[BASEPN],E$6)+SUMIFS(RELEASE[WK11],RELEASE[BASEPN],E$7)+SUMIFS(RELEASE[WK11],RELEASE[BASEPN],E$8)+SUMIFS(RELEASE[WK11],RELEASE[BASEPN],E$9)+SUMIFS(RELEASE[WK11],RELEASE[BASEPN],E$10)+SUMIFS(RELEASE[WK11],RELEASE[BASEPN],E$11)+SUMIFS(RELEASE[WK11],RELEASE[BASEPN],E$12)+SUMIFS(RELEASE[WK11],RELEASE[BASEPN],E$13)+SUMIFS(RELEASE[WK11],RELEASE[BASEPN],E$14)+SUMIFS(RELEASE[WK11],RELEASE[BASEPN],E$15)+SUMIFS(RELEASE[WK11],RELEASE[BASEPN],E$16)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E19:AB29Cell Value>E$3textNO
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
How about
=SUM(SUMIFS(Release[WK2],Release[BASEPN],$E$4:$E$16))
 
Upvote 0
Fluff said:
How about
=SUM(SUMIFS(Release[WK2],Release[BASEPN],$E$4:$E$16))
Click to expand...

That doesn't seem to work. It returns "0". When using "Evaluate Formula" to step through the calculation, it shows "#VALUE!" when it gets to the $E$4:$E$16 portion.

1582316882582.png
 
Upvote 0
It might need array entry, I can't remember.
Enter the cell & press Ctrl Shift Enter
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

JenniferMurphy
  • Question Question
Help with coin-collecting sheet
Replies
3
Views
140
felixstraube
F
F
  • Question Question
Looking to index some data based on multiple criteria, then index further data based on the 1st indexed data results
2
Replies
13
Views
870
KRice
K
J
  • Solved
Long IF Formula Calculation Trouble
Replies
6
Views
212
Alex Blakenburg
A
U
  • Solved
Sort with VBA
Replies
5
Views
321
MARK858
MARK858
D
  • Solved
Chart Help Please
Replies
1
Views
91
dmj120
D

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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