SUM of values using horizontal and vertical criteria of a Table

Hello,

I am trying to create a conversion template - I work in apparel and am trying to view sums for pant sizes in 2 different ways.

BACKGROUND AND CONTEXT

Initially, I am provided a table or create a table to create an overview of a purchase.

Examples:
Figure 1
Screen Shot 2018-10-15 at 2.16.49 PM.png

Figure 2
Screen Shot 2018-10-15 at 2.24.02 PM.png

You can see there is WAIST SIZE on the horizontal header and INSEAM on the vertical header. The Waist size is always ascending but the Inseam is repeating values as they are organized by style and color.

I first like to see it this way as it allows me to easily see and manipulate totals per waist size and totals per inseams, per colors and per style.

THE PROBLEM!!!
What I am trying to solve is how to use a formula to spit out totals overall per Inseam and Waist size only.

Example:
Screen Shot 2018-10-15 at 2.27.57 PM.png

Formula would reference data from figure 1 and 2 to show me overall totals across the board without extra manual effort. So my end goal is to only have to create the Figure 1 & 2 charts, and be able to automatically see my totals.

Sorry if I am overexplaining!! or if I am not providing enough information, please let me know.

Truly appreciate your time and any help or tips.

--David

Originally Posted by d_whys

… You can see there is WAIST SIZE on the horizontal header and INSEAM on the vertical header...

Hi David,

It is virtually impossible to make out any detail in your screen shot, so please attach a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it is broken on this forum.

Hope this helps.

Pete

Hi Pete,

Ah thanks, I attached the sample workbook here from the 3 images.

David,

you can put this formula in cell E62:

=SUMPRODUCT($G$7:$Q$14,($G$6:$Q$6=B62)*($F$7:$F$14=C62)) + SUMPRODUCT($F$22:$P$53,($F$21:$P$21=B62)*($E$22:$E$53=C62))

and then copy it down as required. The first term gives totals from your upper table, and the second term totals the lower table, for the sizes given.

Hope this helps.

Pete

Please marry me, Pete. You are the greatest!!! Thank you so so much :D works like a charm!!

Well, I'm glad it worked for you.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Done and done . Thank you again.