Sum unique with conditions
 

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!


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200509/1

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.

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?

Aladin Akyurek wrote:
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!



--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200509/1

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!

In article <55178f8d2d1d6@uwe, "Brian D via OfficeKB.com" <u9303@uwe
wrote:

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?

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!

Domenic wrote:
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!

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?


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200509/1

Brian D via OfficeKB.com wrote:
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 D2:D4 instead of the sequence 1,
1, and 1.


Domenic wrote:

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$1 1=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!


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?




--

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

In article ,
Aladin Akyurek wrote:

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 D2:D4 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!

Domenic wrote:
In article ,
Aladin Akyurek wrote:


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 D2:D4 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!

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.

In article ,
Aladin Akyurek wrote:

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... :)