Many Columns Many Criteria Help

[BeautyWorld]

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:

Store # Status Class TY LY TY W/O LY W/O 1654 <tbody> </tbody> 1654 1654 3 4 5 6 444 <tbody> </tbody> Comp 2011 3 4 5 6 475 <tbody> </tbody> Comp 2018 3 4 5 6 476 <tbody> </tbody> Comp 2016 3 4 5 6 517 <tbody> </tbody> Open 2012 3 4 5 6 522 <tbody> </tbody> Rose 2015 3 4 5 6 549 <tbody> </tbody> Comp 2016 3 4 5 6 550 <tbody> </tbody> Rose 2017 3 4 5 6 762 <tbody> </tbody> Open 2013 3 4 5 6 763 <tbody> </tbody> Open 2011 3 4 5 6 773 <tbody> </tbody> Rose 2017 3 4 5 6 <tbody> </tbody>

<tbody>
</tbody>

Here would be the 2 topside tabulations:

CLASS TY LY %TY/LY TY W/O LY W/O %TY/LY 2011 2012 2013 2015 2016 2017 2018 1654 Rose Total <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

CLASSIFICATION TY LY %TY/LY TY W/O LY W/O %TY/LY COMP OPEN ROSE 1654 TOTAL <tbody> </tbody>

<tbody>
</tbody>

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!

 

[Michael M]

I'm not at the computer at the moment, but wouldn't Pivot Tables do this for you ???

 

[BeautyWorld]

I'm not at the computer at the moment, but wouldn't Pivot Tables do this for you ???

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!

 

[Michael M]

The data page/s don't have to be affected in any way....simply put the pivot tables on a separate sheet...
Have a look here

https://www.contextures.com/xlPivot08.html