Getting the ROW of a COUNTIF result?

[LR]

Hi

For a cyclingclub i want to make a simple statistic of the members and how
many 1st places, 2nd. plcases and so on, they have had during the season.
I have a excel sheet with all races in a season. In the 1st. coloum i have
the name and in the second coloum the final position in the race.

Name Plc.
xxxx xxxxxxxxx 3
yyyy yyyyyyyyy 1
zzzz zzzzzzzzzzz 5
yyyy yyyyyyyyy 1

The statistic i want to make:
Name Pcl-> 1 2 3 4 5 6
xxxx xxxxxxxxx 0 0 1 0 0 0
yyyy yyyyyyyyy 2 0 0 0 0 0
zzzz zzzzzzzzzzz 0 0 0 0 1 0

I can use the COUNTIF to find each name, but can i then get the ROW (x) of
the COUNTIF findings and sum the Ax numbers for each name?
Maybe i have to get into some macro programming for this to work?

br Lars

 

[Ardus Petus]

Use SUMPRODUCT:
=SUMPRODUCT((Sheet1!$A$1:$A$999=$A2)*(Sheet1!$B$1:$B$999=B$1))

See example: http://cjoint.com/?eqsKxZyHPE

HTH

 

[LR]

Use SUMPRODUCT:
=SUMPRODUCT((Sheet1!$A$1:$A$999=$A2)*(Sheet1!$B$1:$B$999=B$1))

See example: http://cjoint.com/?eqsKxZyHPE

Hi

Thanks, works great!

br Lars