I have an excel sheet, which collects a particular data for different people
and different fields.
It essentially looks like this:
Fields A B C D
aa 1 3 1 2
ab 2 1 1 2
ca 1 2 1 1
dd 1 2 2 2
rd 3 3 1 2
fg 3 4 1 1
ef 4 2 2 3
tf 1 2 2 1
tt 1 2 1 2
sd 2 1 2 2
ss 2 1 2 2
df 1 1 2 2
where the letters in the forst column specify names of persons and A, B, C,
D... specify the field in which the persons have scored the levels(1, 2, 3, 4)

In this sheet, I have 4 more rows at the bottom like this:
Level 1 6
Level 2 3 6
Level 3 2 2
Level 4 1 0

6 in the first row is the number of "Level1"s in A, 3 the no of "level 2"s
in A.
0 at the last is the number of "level 4" in D. and so on....

The question is: I have hundreds of numbers like this in the same sheet. for
all the columns, I cannot go on saying =COUNTIF(B2:B13,1) to find the number
of 1's in field B!
Is there some way to give the formula so that, it calculated the similar
data range in every column and puts the amount in the specified cell?

Its not entirely clear what you want, if it is aformula to count th
number of 1's in A, then 2's in A 3's etc and to also do the same for
C D

try creating a summarytable with your levels down the side, I a
assuming that A35 is the cell with the 1 in below. The formula would b
in cell b35 this formula can be copied right and down for your entir
range

1 Countif(b$2:b$20,$A35)
2
3
4

If it is something else you require, you will have to give a bit mor
explaination

Regards

Da

--
Da
-----------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...fo&userid=2710
View this thread: http://www.excelforum.com/showthread.php?threadid=54537

Try =COUNTIF(B$2:B$13,ROW(A1))

--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

"Kazuki" wrote in message
...
I have an excel sheet, which collects a particular data for different
people
and different fields.
It essentially looks like this:
Fields A B C D
aa 1 3 1 2
ab 2 1 1 2
ca 1 2 1 1
dd 1 2 2 2
rd 3 3 1 2
fg 3 4 1 1
ef 4 2 2 3
tf 1 2 2 1
tt 1 2 1 2
sd 2 1 2 2
ss 2 1 2 2
df 1 1 2 2
where the letters in the forst column specify names of persons and A, B,
C,
D... specify the field in which the persons have scored the levels(1, 2,
3, 4)

In this sheet, I have 4 more rows at the bottom like this:
Level 1 6
Level 2 3 6
Level 3 2 2
Level 4 1 0

6 in the first row is the number of "Level1"s in A, 3 the no of "level 2"s
in A.
0 at the last is the number of "level 4" in D. and so on....

The question is: I have hundreds of numbers like this in the same sheet.
for
all the columns, I cannot go on saying =COUNTIF(B2:B13,1) to find the
number
of 1's in field B!
Is there some way to give the formula so that, it calculated the similar
data range in every column and puts the amount in the specified cell?