Nel ,
Paperback Writer ha scritto:
I need to figure out how to count occurrences (all lined up in two
columns), that will then be displayed in a table. The occurrences
are a series of rankings (1 - 7) spaced over 20 intervals.

The column looks like this.
COLUMN 1 COLUMN 2
1 Sally
2 Sue
3 Sam
4 Suzy
5 Sherri
6 Sandra
7 Serena

The above is repeated twenty times. The COLUMN 1 will have "1" in
the very next row (say A9), with a different order for the seven
girls.

I created a table that looks like this

NAME 1 2 3 4
5 6 7
SALLY
SUE
SAM
SUZY
SHERRI
SANDRA
SERENA

I need a formula that will populate how many times each girl was
selected first, second, third.... all the way to seventh.

SALLY might look like this.

NAME 1 2 3 4 5
6 7 SALLY 5 3 2 5
1 3 1

I tried creating a pivot table, but the pivot table is problematic
since
this order is randomly generated by using the F9 key. Whenever I
'refresh' the data in the pivot table, it re-randomize the numbers.
The pivot table is then displaying the old numbers. This is for a
report, so all the numbers must match. In short, I need this to run
from a formula, and not a pivot table.

THANKS!!!!

Hi Paperback Writer,

you can use a SUMPRODUCT formula, like this:

=SUMPRODUCT(($B$1:$B$34=$E5)*($A$1:$A$34=F$4))

whe

$A$1:$A$34 is the ranking for the girls

$B$1:$B$34 is the list with the all the entries of girls' names;

$E5 is the first name in the table;

F$4 is the first number in the table.

You will have a table like this:

1 2 3 4 5 6 7
Sally
Sue
Sam
Suzy
Sherri
Sandra
Serena

where Sally is in E5 while number 1 is in F4. So the above formula should be
written in F5. Then you can copy the formula across the columns and the rows
of the table.


--
Hope I helped you.


Thanks in advance for your feedback.


Ciao


Franz Verga from Italy