Let A2:C12 house the sample you provided.

In E2 enter & copy down:

=A2&"#"C2

Then invoke in G2 and copy down:

=SUMPRODUCT(--($C$2:$C$12=F2),($A$2:$A$12<"")/COUNTIF($E$2:$E$12,$E$2:$E$12&""),$D$2:$D$12)

where F2:F3 houses ACME and WIDGET.



Brian D via OfficeKB.com wrote:
Hey all,

I am looking for a function or group of functions that will:
Match Column C and for every unique item in column A, it will sum column D.
Example:

Column A: Column B: Column C: Column D:
12346 Level1 ACME 1
12346 Level1 ACME 1
12346 Level2 ACME 1
12376 Level2 ACME 2
13255 Level2 ACME 1
13548 Level1 ACME 4
12356 Level1 WIDGET 5
12356 Level2 WIDGET 5
12376 Level2 WIDGET 6
12378 Level3 WIDGET 2
12378 Level1 WIDGET 2

So if I run the formula for ACME, it should come back with: 8
And if I run the formula for Widget, it should come back with: 13

Not sure if this is possible, but any help is appreciated.
Thanks!



--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.