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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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
210
Tetra201
Tetra201
GMC The Macro Man
  • Question Question
Count with Multiple Criteria - Follow up
Replies
2
Views
210
myall_blues
myall_blues
M
  • Question Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
568
mrshuck
M
T
  • Question Question
Converting Time Start/Stop Ranges to Presence/Absence
Replies
6
Views
258
myall_blues
myall_blues
H
  • Solved
Creation of arrows for 2 family trees.
Replies
8
Views
372
harzer
H

Forum statistics

Threads
1,224,820
Messages
6,181,154
Members
453,021
Latest member
Justyna P

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