Formula Help

[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

 

[Max]

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

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

 

[Guest]

highlight the data in both columns and go data/subtotals

 

[JulieD]

just a note, if you use data / subtotals - ensure that you sort first on the
Order number.