Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel same Formula in different columns-calculate columnwise?
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel same Formula in different columns-calculate columnwise?
Hi
You should be able to drag the formula across once it's right. It will automatically increase the column reference for you. To drag the formula across, use the little + sign in the bottom right-hand corner. Hope this helps. Andy. "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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel same Formula in different columns-calculate columnwise?
I have answered this on the other post. It it bad practice to post things twice, as it wastes your time (you have to look in 2 places for your answers), wastes our time reading unnecessary posts and stops us reading potentially useful contributions from other contributors. This means that you may be prevented from getting the best answer Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=545383 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel same Formula in different columns-calculate columnwise?
Thanks Dav, I am new to this forum and thought I'll get a prompt reply by
posting in 2 places, getting noticed by different users. I was in a hurry. I would follow that here after! "Dav" wrote: I have answered this on the other post. It it bad practice to post things twice, as it wastes your time (you have to look in 2 places for your answers), wastes our time reading unnecessary posts and stops us reading potentially useful contributions from other contributors. This means that you may be prevented from getting the best answer Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=545383 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel same Formula in different columns-calculate columnwise?
Thanks Andy, it helps. and it's very easy too.
"Andy" wrote: Hi You should be able to drag the formula across once it's right. It will automatically increase the column reference for you. To drag the formula across, use the little + sign in the bottom right-hand corner. Hope this helps. Andy. "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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel formula counting date to date in 4 columns | Excel Worksheet Functions | |||
How do i copy columns of data in notepad into microsoft excel? | Excel Discussion (Misc queries) | |||
How do i copy columns of data in notepad into microsoft excel? | Excel Discussion (Misc queries) | |||
How do i copy columns of data in notepad into microsoft excel? | Excel Discussion (Misc queries) | |||
Suddenly Excel can't calculate formula!!! | Excel Worksheet Functions |