Formula Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two colums. One contains Order#, the other the $ amount the of a
discount. If an order has 5items in it, the Order# appear in column A 5 times
with the associated discount for each item. I want to calculate the total
discount by Order#.

Data:

Full Order # Difference

P10890950001 -$5.00
P10962970001 -$13.00
P10948610001 -$25.00
P10948610001 -$15.00
P10731350001 -$10.00
P10731350001 -$4.50
P09494270003 -$20.00
P10878810001 -$12.29
P10866140001 -$39.47
P09604470002 -$22.00
P10895690001 -$6.00
P10895690001 -$6.00
 
Maybe try a pivot table (PT) ?
Takes only a few clicks ..

Put the cursor anywhere within the source table
Full Order # Difference
P10890950001 -$5.00
P10962970001 -$13.00
Click to expand...
etc

Click Data > Pivot Table Report > Next > Next

In step 3 of the wizard:
Drag Full Order # and drop within ROW area
Drag Difference and drop within DATA area
(It'll appear as Sum of Difference)
Click Finish

The PT will be created in a new sheet to the left, and appear as:

Sum of Difference
Full Order # Total
P09494270003 20
P09604470002 22
P10731350001 14.5
P10866140001 39.47
P10878810001 12.29
P10890950001 5
P10895690001 12
P10948610001 40
P10962970001 13
Grand Total 178.26
 
just a note, if you use data / subtotals - ensure that you sort first on the
Order number.
 
Back
Top