sum value column b only if value in column a is unique

Question

looking for direction on how to do the following, thank you in advance for any insight provided:

I want to sum the total values in column C (Total Lines per order) ONLY once per the value in column A (Sales Order). So Sales Order "A" appears three times in column A, however I only want to sum the corresponding value in column C one time as part of the sum, and not include the duplicate lines.

I could use the "Delete Duplicates" function, but I want to keep the unique item Part Numbers on each line on the report.

Example:

Sales Order "A" has 3 total Lines
Sales Order "B" has 4 total lines
Sales order "C" has 2 total lines
Sales Order "D" has 5 total lines          
                  = 14 total lines

I've attached an example list in the link Below

You can use VBA with `Dictionary` object to achieve this – Shai Rado Jul 13 '17 at 14:04 — Shai Rado, Jul 13 '17 at 14:04
You forgot E, total lines is 16 with E. – Scott Craner Jul 13 '17 at 14:15 — Scott Craner, Jul 13 '17 at 14:15

score 1 · Accepted Answer · answered Jul 13 '17 at 14:14

1

The formula you want is:

=SUMPRODUCT(C2:C14/COUNTIF(A2:A14,A2:A14))

answered Jul 13 '17 at 14:14

Scott Craner

148,073
10
49
81

Keeping it simple, I like it. You don't even ignore duplicates, you just divide them out. This deserves two +1s. – CLR Jul 13 '17 at 14:34

sum value column b only if value in column a is unique

1 Answers1