Sum based on two conditions

Hello! Please help with data below - sum col B based on top 2 of col C and
group by Col A. T1=70+100=170, T2 = 400+300=700, T3=150+110=260

Thank you so much for helping.

Col A Col B Col C
T1 100 20%
T3 150 15%
T2 400 30%
T1 400 10%
T1 70 50%
T2 120 18%
T1 150 15%
T2 300 30%
T3 110 10%

If your data is in A1:C9 and

A13 = T1
A14 = T2
A15 = T3

Enter this in B13 (Confirmed with Control+Shift+Enter after typing it into
the formula bar-if you do it right excel will put braces { } around the
formula) and copy down.

=SUM(($A$1:$A$9=A13)*($C$1:$C$9>=LARGE(IF($A$1:$A$9=A13,$C$1:$C$9,0),2))*($B$1:$B$9))


"luvgreen" wrote:

> Hello! Please help with data below - sum col B based on top 2 of col C and
> group by Col A. T1=70+100=170, T2 = 400+300=700, T3=150+110=260
>
> Thank you so much for helping.
>
> Col A Col B Col C
> T1 100 20%
> T3 150 15%
> T2 400 30%
> T1 400 10%
> T1 70 50%
> T2 120 18%
> T1 150 15%
> T2 300 30%
> T3 110 10%
>
>

Assuming that A2:C10 contains the data, let E2:E4 contain T1, T2, and
T3, then enter the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER, in F2 and copy down:

=SUM(IF(A$2:A$10=E2,IF(ISNUMBER(MATCH(C$2:C$10,LARGE(IF(A$2:A$10=E2,C$2:C
$10),{1,2}),0)),B$2:B$10)))

Note that if your data contains the following...

T1 100 20%
T3 150 15%
T2 400 30%
T1 400 20%
T1 70 20%
T2 120 18%
T1 150 10%
T2 300 30%
T3 110 10%

....where the top percentage for T1 is 20%, and there are 3 instances of
20% for T1, the formula will return 570.

In article <[email protected]>,
luvgreen <[email protected]> wrote:

> Hello! Please help with data below - sum col B based on top 2 of col C and
> group by Col A. T1=70+100=170, T2 = 400+300=700, T3=150+110=260
>
> Thank you so much for helping.
>
> Col A Col B Col C
> T1 100 20%
> T3 150 15%
> T2 400 30%
> T1 400 10%
> T1 70 50%
> T2 120 18%
> T1 150 15%
> T2 300 30%
> T3 110 10%
>

Thank you so very much for your kind help. I appreciate it.

"JMB" wrote:

> If your data is in A1:C9 and
>
> A13 = T1
> A14 = T2
> A15 = T3
>
> Enter this in B13 (Confirmed with Control+Shift+Enter after typing it into
> the formula bar-if you do it right excel will put braces { } around the
> formula) and copy down.
>
> =SUM(($A$1:$A$9=A13)*($C$1:$C$9>=LARGE(IF($A$1:$A$9=A13,$C$1:$C$9,0),2))*($B$1:$B$9))
>
>
> "luvgreen" wrote:
>
> > Hello! Please help with data below - sum col B based on top 2 of col C and
> > group by Col A. T1=70+100=170, T2 = 400+300=700, T3=150+110=260
> >
> > Thank you so much for helping.
> >
> > Col A Col B Col C
> > T1 100 20%
> > T3 150 15%
> > T2 400 30%
> > T1 400 10%
> > T1 70 50%
> > T2 120 18%
> > T1 150 15%
> > T2 300 30%
> > T3 110 10%
> >
> >

Be sure to note Domenic's caveat about more than 2 values being included in
the formula if the percentages are duplicated. It would affect the formula I
posted as well.

"luvgreen" wrote:

> Thank you so very much for your kind help. I appreciate it.
>
> "JMB" wrote:
>
> > If your data is in A1:C9 and
> >
> > A13 = T1
> > A14 = T2
> > A15 = T3
> >
> > Enter this in B13 (Confirmed with Control+Shift+Enter after typing it into
> > the formula bar-if you do it right excel will put braces { } around the
> > formula) and copy down.
> >
> > =SUM(($A$1:$A$9=A13)*($C$1:$C$9>=LARGE(IF($A$1:$A$9=A13,$C$1:$C$9,0),2))*($B$1:$B$9))
> >
> >
> > "luvgreen" wrote:
> >
> > > Hello! Please help with data below - sum col B based on top 2 of col C and
> > > group by Col A. T1=70+100=170, T2 = 400+300=700, T3=150+110=260
> > >
> > > Thank you so much for helping.
> > >
> > > Col A Col B Col C
> > > T1 100 20%
> > > T3 150 15%
> > > T2 400 30%
> > > T1 400 10%
> > > T1 70 50%
> > > T2 120 18%
> > > T1 150 15%
> > > T2 300 30%
> > > T3 110 10%
> > >
> > >