Trying to build loop with conditional sums

Hello All,

I've been spending several hours to no avail in attempt to write a script to do some conditional sums and deletion of rows and was hoping to find some ideas on here.

Problem: I am trying to sum the numbers in column's A, B and C that relate to the same item number but only at locations D1 or any location beginning with an S per the table below. In this example, the numbers in the rows with D2 or D3 can be ignored. After the number's are summed, I would like to paste them all in one row and delete any extraneous rows.

Sample Table before modification:

Item Location A B C D E F
12345 D1 2 4 45 # # #
12345 D2 4 5 6 # # #
12345 D3 6 2 4 # # #
12345 S1 8 2 3 # # #
12345 S2 2 7 3 # # #
111111 D1 4 8 5 # # #
111111 D2 2 43 8 # # #
111111 S3 1 3 9 # # #


Desired Output:

Item Location A B C D E F
12345 D1 12 13 51 # # #
111111 D1 5 11 14 # # #


Anyone have any thoughts on how to best approach this?

I have been able to write some VBA to delete any extraneous rows that don't need to be summed (Per example, locations D2 and D3) but I can still not figure out how to sum the remaining numbers to make the output look like the desired output above. I'm presuming I need to make some sort of temporary table or loop to do the math in?

Any thoughts or direction would be appreciated!

Mike

I've definitely tried that! The problem I've run in to there would be where I have put the general # signs above, there are other values I do not want to be summed, I would like those values to remain the same as they are in the rows with the location D1. (I should have mentioned that before).

I tried to attach an example spreadsheet but my connection is timing out this morning..

Or is there a way in a pivot table to subtotal certain columns and keep others as the value of a specific cell/row that you may know of?

Thanks!