Sum unique with conditions

[Brian D via OfficeKB.com]

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!

 

[Aladin Akyurek]

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.

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.

 

[Brian D via OfficeKB.com]

Thanks Aladin,
It seems to be working. I had to add another & in =A2&"#"&C2
I'll have to try it on my larger data sheet. Is there any way to do this
without having to copy the two lines down the entire data sheet?

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.

Hey all,

[quoted text clipped - 20 lines]

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

 

[Domenic]

Assuming that F2:F3 contains ACME and WIDGET, try the following...

G2, copied down:

=SUMPRODUCT(--($A$1:$A$11<>""),--($C$1:$C$11=F2),--(MATCH($A$1:$A$11&"#"&
$C$1:$C$11,$A$1:$A$11&"#"&$C$1:$C$11,0)=ROW($A$1:$A$11)-ROW($A$1)+1),$D$1
:$D$11)

or

=SUM(IF(FREQUENCY(IF(($A$1:$A$11<>"")*($C$1:$C$11=F2),MATCH($A$1:$A$11&"#
"&$C$1:$C$11,$A$1:$A$11&"#"&$C$1:$C$11,0)),ROW($A$1:$A$11)-ROW($A$1)+1)>0
,$D$1:$D$11))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Brian D via OfficeKB.com]

That's what I was looking for. I began to have problems with the Countif
bombing out because of too many rows. This seems to work just fine.
Thanks!

 

[Aladin Akyurek]

That's what I was looking for. I began to have problems with the Countif
bombing out because of too many rows. This seems to work just fine.
Thanks!

Yes, COUNTIF can be very problematic with large ranges.

I opted for the formula I posted for the reason that multiple instances
of a lookup value like ACME would have different values in D instead of
all 1's as in your sample for you didn't rule that out. So, the
alternative formulas Domenic posted will pick out the first asscociated
value. Try out them with 5, 1, and 1 in D24 instead of the sequence 1,
1, and 1.

--

[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.

 

[Domenic]

Yes, COUNTIF can be very problematic with large ranges.

Wow! I had not idea! Nice to know!

I opted for the formula I posted for the reason that multiple instances
of a lookup value like ACME would have different values in D instead of
all 1's as in your sample for you didn't rule that out. So, the
alternative formulas Domenic posted will pick out the first asscociated
value. Try out them with 5, 1, and 1 in D24 instead of the sequence 1,
1, and 1.

Yes, I was under the impression that the multiple instances associated
with a lookup value were duplicates.

Thanks Aladin!

 

[Aladin Akyurek]

Wow! I had not idea! Nice to know!




Yes, I was under the impression that the multiple instances associated
with a lookup value were duplicates.

Thanks Aladin!

That impression is hard to maintain though when:

12346 Level1 ACME 1
12346 Level1 ACME 1
12346 Level2 ACME 1

where the 3d record differ (Level2 vs Level1) from the first too. Maybe
A-C-D only are considered for the duplicate record definition.

 

[Domenic]

That impression is hard to maintain though when:

12346 Level1 ACME 1
12346 Level1 ACME 1
12346 Level2 ACME 1

where the 3d record differ (Level2 vs Level1) from the first too.

Most definitely! Based on the information provided, I shouldn't have
made that assumption. At the very least, I should have stated that
assumption explicitly.

Maybe > A-C-D only are considered for the duplicate record definition.

That's quite possible. I'd be interested in the final 'verdict' from
the OP...