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
--
AP

"LR" <[email protected]> a écrit dans le message de
news:%[email protected]...
> 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
>
>
>

[LR]

"Ardus Petus" <[email protected]> wrote in message
news:[email protected]...
> 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