Including product data on same line as totals when using Subtotal function?

Situation: We store multiple pallets of the same material. When running an inventory report, the system puts each pallet on its own spreadsheet row, because it is a separate "location."

I work with totals of material. So even though we're holding, say, 10 pallets, I only need to know/show the total of that product on one line.

I've been using the Subtotal functionality -- "At each change in Product ID, subtotal Stock QTY".

However, on the resulting subtotal lines, only the Product ID and the Stock QTY are shown -- all the other text data cells (product name, length, width, etc.) do not appear on that row.

As a result, when you "close up" all the sections to show only totals, there are a bunch of cells that are blank.

I've been manually copying the data into the blank cells on the Subtotal rows, then copying only the subtotal rows to a new sheet -- but it's killin' me.

So -- I need a way to generate subtotals along with all the product data in the same row.

... And I'm thinking the Subtotal functionality is not the best way to do this ...

Any suggestions?

See attached screen shots and sample spreadsheet. In the spreadsheet, the raw data is on the first tab, subtotaled data is on second tab.

(This is my first time posting for help, so I apologize if I've left anything out!)

excel_01.jpg

excel_02.jpg

excel_03.jpg

excel_04.jpg

Welcome to the forum.

Unless you really need to have the totals in the same table, then I suggest doing the totals elsewhere. You can do this on the same sheet, or on another sheet. The steps below assume you use the same sheet.

1. First, copy all your column headings to new columns. For visibility, I suggest leaving a blank column H, then using columns I-O.

2. Now you can generate a list of the distinct ID numbers in column I, with this formula:

Formula:

Please Login or Register  to view this content.

Important: this is an array formula so needs to be entered using Ctrl-Shift-Enter not just Enter. You will know the array is active when you see curly brackets { } appear around your formula - don't try to enter them yourself. If you do not Ctrl-Shift-Enter an array formula you will get an error or a clearly incorrect result (in this case, you'll get 'end of list' as your first result). If that happens, just click into the formula bar and try again.

Once entered correctly, drag it down until you get 'end of list'. You can drag it further if you want, to allow for your data growing - you will just get multiple 'end of list' entries - which is not a problem (and which you can use later...)

3. Now you can do some simple lookups to fill in the product name, colour, etc. Put this in J2, then drag across to N2, then down as far as you dragged down the previous formula in step 2:

Formula:

Please Login or Register  to view this content.

Be careful with the placement of the $ signs, which make columns and/or rows into absolute references.

4. Now you can get the totals for each. Put this in O2 and drag down:

Formula:

Please Login or Register  to view this content.

5. Now you have the totals for each product. You can, if you want, then just do a manual SUM at the bottom, but I think you'd probably like the grand total automatically?
To do this, you can use the 'end of list' as a marker to do a total in column O. Change the formula in O2 to this one and drag down:

Formula:

Please Login or Register  to view this content.

What this does is check if you've reached the end of the list (that's the middle IF) and give you a SUM of column O to that point. The first IF checks to see if you've gone beyond the end of the list and returns blanks if you have.

6. Just to finish off, you might want to show 'Grand total' against your final Sum. You can do this by adding the same pair of IF checks to column N, but instead of inserting a Sum, you insert the words 'Grand total' - replace the previous formula in N2 with this one and drag down:

Formula:

Please Login or Register  to view this content.

You'll need to adjust your ranges for your real raw data, of course - the formulae above use the row 2-22 range of your sample data.
I've attached your file with an extra sheet showing this all working.

Hope that does what you want.