Sum of all numbers before a blank row

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

nymyth - 25 Jun 2007 15:43 GMT

I have an excel file which i have sorted by column 'Ordered Item'.
Next to this column is a 'Price' column. The 'Ordered Item' Column
may have duplicates. What I need to do is put a subtotal beside each
group of 'Ordered Items' (even if they are single items). How would I
sum the Prices every time there is a change in the data in column
'Ordered Items'?

Ordered Items Price
241 50
241 50
340 25
600 100
600 100

So if the sheet looks like this, how can I sum or subtotal the price
column whenever there is a change in data in the Ordered Items
column. So sum of 241 shows up 100, 340 shows up as 25 and 600 shows
up as 200.

Thanks.

Reply to this Message

nymyth - 25 Jun 2007 15:45 GMT

I did manage to enter a blank row after a data change in the 'Ordered
Items' list. I also need to be able to sort in descending order the
subtotals list.

Reply to this Message

Dave Peterson - 25 Jun 2007 17:34 GMT

I'd drop the insertion of the blank row.

Then sort your range (if you haven't) and add headers (if you haven't).

Then select the range (both columns)
data|subtotals
and excel will insert a new row that has the subtotal (use Sum as your function)
for each value in that key column.

You can use the outlining symbols to the left to hide the details (or show
them).

Another option would be to use data|pivottable. Your data doesn't have to be
sorted to get a very nice summary.

> I did manage to enter a blank row after a data change in the 'Ordered
> Items' list. I also need to be able to sort in descending order the
> subtotals list.

Signature

Dave Peterson

Reply to this Message

nymyth - 25 Jun 2007 17:44 GMT

wow, didnt even notice that feature. Thanks Dave. Works great.

Reply to this Message