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 . 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!
[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!