0

I have an Excel 2016 Spreadsheet of Signs with their part numbers, quantity and wording. I need to sub-total the quantity of each part number (Section A in the attached bitmap). I would like to be able to see just the subtotals of the data, showing the Part#, Qty and Desc of each subtotal. Using the Subtotal function on the Data tab and choosing "Description" as the "for each change in" doesn't show the Part number (Section B of the bitmap); using "Part#" as the "for each change in" doesn't show the Description field. Is there an easy way to get Excel to show me both fields next to the Subtotal?

views of subtotals

Thanks in advance! Bill

2
  • Welcome to Super User. What have you tried so far Bill? – spikey_richie Jun 4 '19 at 13:35
  • Hi, spikey_richie -- I tried unchecking "summary below data" and that resulted in (incorrect, actually) subtotals in the bottom cell of each change. I also tried manually adding the desc. to the corresponding cell subtotal had created, which actually worked, but it's a HUGE spreadsheet so doing every subtotal would be out of the question. – user2965168 Jun 4 '19 at 14:12
0

In picture 'B', put this 'modified' index match formula in cell D5444, then drag downwards.

=INDEX(A:A,MATCH(1,INDEX(B5444=B:B)*(C5444=C:C),0,1)-1,0))

Idea :

use the normal index+match, and edit it to load the A5443 value instead of A5444. (add -1)

Hope it solves.

2
  • Thanks, @p_pyhidot_ , but apparently I'm too ignorant of Excel to get that to work. copying out your formula, pasting it into cell D5444 and dragging downward only resulted in the cells being filled with the formula, not the result of same. (I would attach a jpeg, but I'm also too ignorant of how the forum works too. Obviously in over my head, haha! Thanks anyway!) – user2965168 Jun 11 '19 at 11:40
  • "pasting it into cell D5444 and dragging downward" <-- this is only to be done after you had filter it exactly like picture B, not before. | "resulted in the cells being filled with the formula, not the result of same." <-- without the filter as applied on the data on picture B, this is the kind of result you'll get. | That is..if "to get Excel to show me both fields next to the Subtotal" is your main goal.. If there is something else/more/different, do mention it all (by editing it) in the question. – p._phidot_ Jun 11 '19 at 14:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.