Get a sum total based on three criteria

I've got a spreadsheet with headers in column A and in rows 9 and 10. The headers in column A start in row 11 and are sizes (i.e XS, S, M, etc..). The headers in row 9 start in column B and are names (ie. First, Second, Third). The headers in Row 10 can be either Net Sales or On Hand Units. There are then values starting in B11 to L19.

The headers in column A do not repeat but the headers in row's 9 and 10 can repeat. What I need to do is create a function for a report page that will total all of the values that match to a particular header in column A, the header in row 9 and the header in row 10. So for example I would want to get the total for any instance when the header in column A is M, the header for row 9 is Second, the header for row 9 is Net Sales.

I've attached a sample of what my data dump would look like as well as the separate reporting worksheet where I want the totals to appear. Thanks in advance for any help

Data set-up here is crucial.

First it is imperative that your headers on Report tie out to those on 'Data Dump' -- so I'd draw your attention to the following:

a) Headers on 'Report' should read: Net Sales Units & On-Hand Units
(example file is missing Units for Net Sales and a Hyphen for On-Hand Units)

b) You're missing size "O/S"

Second on 'Data Dump' you're life will be made far far simpler if you repeat FIRST/SECOND etc in the blank cells on row 9, ie:

Wherever you have blank cell in Row 9 but value in Row 10 you should ensure the preceding value in Row 9 is placed in the blank cell ... if you don't do this you will find your task becomes far more complex especially given you don't have fixed number of columns per "group" -- ie some have 1 column some 2 columns... making use of MOD or offset virtually impossible.

The insertion of values into blanks can be done quite quickly doing the following:

Highlight B9:L9 (using your example file)
Edit -> GoTo -> Special Cells -> Blanks -> Click OK
Now type = then left arrow then whilst holding CTRL press ENTER
(The above will ensure any/blanks in the selection assume the preceding value)

Once you have done the above you can then apply a SUMPRODUCT to your 'Report' sheet such that:

B3: =SUMPRODUCT(('Data Dump'!$A$11:$A$20=IF(ISEVEN(COLUMN()),B$1,A$1))*('Data Dump'!$B$9:$Z$9=$A3)*('Data Dump'!$B$10:$Z$10=B$2)*('Data Dump'!$B$11:$Z$20))
Note I've used column Z -- as I'm guessing as data is added it does so in a horizontal fashion as opposed to vertical (ie vertical range is fixed based on sizes) -- so by using Z you give yourself a little excess capacity.
Adjust to suit your needs but don't make overly large as Sumproduct is pretty hefty formula.

This can be copied across the entire matrix range (B3:Q5) -- I say Q because I presume you will add O/S to your list of sizes (ie add 2 columns of data to Report than exist in your sample).

Obviously were the data on 'Data Dump' in a friendlier state you could just use a Pivot Table and avoid the Sumproduct altogether.

Anyway I hope the above points you in the right direction.

post up the file as you have it now.

Usually that error appears if there is a typo in the formula.. did you change anything in the formula?... Check that the cell references are all typed correctly and that the function names are typed correctly...

If I copy paste the formula above, I get no errors...

ISEVEN() is a Analysis Toolpak Addin function.. that's why you are getting #NAME error... you don't have it installed and Excel won't recognize the function.

Go to Tools|Addins and select Analysis Toolpak to install....

or replace the ISEVEN() function with MOD()

e.g.

Please Login or Register  to view this content.

Thanks for the pick-up NBVC.

No problem... I didn't catch the ISEVEN() function in there the first time...

and not an N() in sight

Can you please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save