Many Columns Many Criteria Help

B

BeautyWorld

New Member
Joined
Aug 20, 2018
Messages
2
Hello! I have a sheet that has many columns with many criteria per column to reference. Then I am rolling the data up to a topside page to subtotal all of the criteria required. My issues is I could not think of a quick way or nested formula way to accomplish this. Instead I am using a ridiculous amount of SUMIF formulas and then adding them up weekly. You can see how taxing this would be when I am reporting on the year, and my current predicament :banghead: . Does anyone know how to sum based on the multiple criteria in multiple columns? I would really appreciate some wisdom in this. This would be a very small example of the data page with the criteria:
[TABLE="width: 1394"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Store #[/TD]
[TD]Status[/TD]
[TD]Class[/TD]
[TD]TY[/TD]
[TD]LY[/TD]
[TD]TY W/O[/TD]
[TD]LY W/O[/TD]
[/TR]
[TR]
[TD][TABLE="width: 134"]
<tbody>[TR]
[TD="class: xl65, width: 134, align: right"]1654[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1654[/TD]
[TD]1654[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][TABLE="width: 134"]
<tbody>[TR]
[TD="class: xl65, width: 134, align: right"]444[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Comp[/TD]
[TD]2011[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][TABLE="width: 134"]
<tbody>[TR]
[TD="class: xl65, width: 134, align: right"]475[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Comp[/TD]
[TD]2018[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][TABLE="width: 134"]
<tbody>[TR]
[TD="class: xl65, width: 134, align: right"]476[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Comp[/TD]
[TD]2016[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][TABLE="width: 134"]
<tbody>[TR]
[TD="class: xl65, width: 134, align: right"]517[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Open[/TD]
[TD]2012[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][TABLE="width: 134"]
<tbody>[TR]
[TD="class: xl65, width: 134, align: right"]522[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Rose[/TD]
[TD]2015[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][TABLE="width: 134"]
<tbody>[TR]
[TD="class: xl65, width: 134, align: right"]549[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Comp[/TD]
[TD]2016[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][TABLE="width: 134"]
<tbody>[TR]
[TD="class: xl65, width: 134, align: right"]550[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Rose[/TD]
[TD]2017[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][TABLE="width: 134"]
<tbody>[TR]
[TD="class: xl65, width: 134, align: right"]762[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Open[/TD]
[TD]2013[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][TABLE="width: 134"]
<tbody>[TR]
[TD="class: xl65, width: 134, align: right"]763[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Open[/TD]
[TD]2011[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][TABLE="width: 134"]
<tbody>[TR]
[TD="class: xl65, width: 134, align: right"]773[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Rose[/TD]
[TD]2017[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Here would be the 2 topside tabulations:
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 521"]
<tbody>[TR]
[TD][TABLE="width: 585"]
<tbody>[TR]
[TD]CLASS[/TD]
[TD]TY[/TD]
[TD]LY[/TD]
[TD]%TY/LY[/TD]
[TD]TY W/O[/TD]
[TD]LY W/O[/TD]
[TD]%TY/LY[/TD]
[/TR]
[TR]
[TD="align: right"]2011[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2012[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1654[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rose[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 535"]
<tbody>[TR]
[TD]CLASSIFICATION[/TD]
[TD] TY[/TD]
[TD]LY[/TD]
[TD]%TY/LY[/TD]
[TD]TY W/O[/TD]
[TD]LY W/O[/TD]
[TD]%TY/LY[/TD]
[/TR]
[TR]
[TD]COMP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OPEN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ROSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1654[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TOTAL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


What formulas could I use to roll up all of this information on the data page into the format above in each cell? I appreciate your help!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
I'm not at the computer at the moment, but wouldn't Pivot Tables do this for you ???
 
Upvote 0
Michael M said:
I'm not at the computer at the moment, but wouldn't Pivot Tables do this for you ???
Click to expand...

The only issue is I need the data page to remain in the previously mentioned format. From my experience with pivots the information has to be presented in your data sheet all vertically to access where my data tab has to be horizontal because it exists as an additional point of reference and not just a hidden tab. Let me know if you can think of an alternative way I am not thinking of for pivot. Thank you!
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Add together figures for Year-To-Date (with criteria)
Replies
3
Views
185
Tetra201
Tetra201
GMC The Macro Man
  • Question Question
Count with Multiple Criteria - Follow up
Replies
2
Views
202
myall_blues
myall_blues
M
  • Question Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
552
mrshuck
M
T
  • Question Question
Converting Time Start/Stop Ranges to Presence/Absence
Replies
6
Views
255
myall_blues
myall_blues
H
  • Solved
Creation of arrows for 2 family trees.
Replies
8
Views
368
harzer
H

Forum statistics

Threads
1,223,896
Messages
6,175,259
Members
452,626
Latest member
huntinghunter

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