Critical SUM UP!!!!!

[excelindianfanclub]

Column1	TEAM	SCORE
Kings Team2	Kings Team	154
Kings Team1	Kings Team	185
Alpha Team1	Alpha Team	182
Dragon Team1	Dragon Team	218
Altas Team1	Altas Team	234
Kings Team2	Kings Team	226
Alpha Team2	Alpha Team	232
Dragon Team2	Dragon Team	231
Kings Team3	Kings Team	223
Alpha Team3	Alpha Team	221
Altas Team2	Altas Team	209
Kings Team4	Kings Team	245
Kings Team5	Kings Team	184
Dragon Team3	Dragon Team	260
Altas Team3	Altas Team	238
Kings Team6	Kings Team	189
Altas Team4	Altas Team	260
Altas Team5	Altas Team	225
Altas Team6	Altas Team	254

<colgroup><col><col><col></colgroup><tbody>
</tbody>

My friend gave me a table consist of team name and score as shown above.My friend asked me to add the scores of team in three different manner
1)add first two values,three values,... of particular team say here add up first two scores of alpha team
2)add two particular values ..say add up second score and fifth score of Altas team
3)add from Kth score to Mth score... say add up scores from second to fourth score of kings team.

i made separate column(which is Column 1 here).used formula
E4&COUNTIF($E4:$E$5,E4) for a table consists of values from D4:F22.

i can't find solutions..i wasted 4 hours already.. please help me. thanks in advance.

 

[excelindianfanclub]

sum up the scores without sorting the dataset

 

[Domenic]

The following assumes that A2:A20 contains the team, and B2:B20 contains the corresponding score...

1)add first two values,three values,... of particular team say here add up first two scores of alpha team

=SUM(IF(ROW(A2:A20)<=SMALL(IF(A2:A20="Alpha Team",ROW(A2:A20)),2),IF(A2:A20="Alpha Team",B2:B20)))

...confirmed with CONTROL+SHIFT+ENTER.

2)add two particular values ..say add up second score and fifth score of Altas team

=SUM(IF(ISNUMBER(MATCH(ROW(A2:A20),SMALL(IF(A2:A20="Atlas Team",ROW(A2:A20)),{2,5}),0)),B2:B20))

...confirmed with CONTROL+SHIFT+ENTER.

3)add from Kth score to Mth score... say add up scores from second to fourth score of kings team.

If, for example, you want to add from the 2nd to the 4th score, try...

=SUM(IF(ROW(A2:A20)>=SMALL(IF(A2:A20="Kings Team",ROW(A2:A20)),2),IF(ROW(A2:A20)<=SMALL(IF(A2:A20="Kings Team",ROW(A2:A20)),4),IF(A2:A20="Kings Team",B2:B20))))

or

=SUM(IF(ISNUMBER(MATCH(ROW(A2:A20),SMALL(IF(A2:A20="Kings Team",ROW(A2:A20)),{2,3,4}),0)),B2:B20))

Note that for the last formula, the array constant {2,3,4} determines that you want to add the 2nd, 3rd, and 4th scores. Change this accordingly.

Also, note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER.

 

[excelindianfanclub]

dear Domenic

thanks for ur help.. i had a issue with the second case.. im able to make a cell reference for team name but unable to call cell reference for 2 and 5 in the formula provided by u (shown in red color).. how to overcome this issue. .. thanks in advance

=SUM(IF(ISNUMBER(MATCH(ROW(A2:A20),SMALL(IF(A2:A20="Atlas Team",ROW(A2:A20)),{2,5}),0)),B2:B20))

 

[Domenic]

If the cells are adjacent to each other, for example D2 contains 2 and E2 contains 5, try...

=SUM(IF(ISNUMBER(MATCH(ROW(A2:A20),SMALL(IF(A2:A20="Atlas Team",ROW(A2:A20)),D2:E2),0)),B2:B20))

If the cells are not adjacent to each other, for example D2 contains 2 and F2 contains 5, try...

=SUM(IF(ISNUMBER(MATCH(ROW(A2:A20),SMALL(IF(A2:A20="Atlas Team",ROW(A2:A20)),CHOOSE({1,2},D2,F2)),0)),B2:B20))

If you have more than two that you wish to sum and they are not in adjacent cells, for example 2nd (D2), 5th (F2), and 8th (H2), try...

=SUM(IF(ISNUMBER(MATCH(ROW(A2:A20),SMALL(IF(A2:A20="Atlas Team",ROW(A2:A20)),CHOOSE({1,2,3},D2,F2,H2)),0)),B2:B20))

Note that these formulas also need to be confirmed with CONTROL+SHIFT+ENTER.

 

[excelindianfanclub]

dear Domenic,
thanks for ur reply.. it much helped me..

 

[Domenic]

You're very welcome!