Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I have exported an Access crosstab query into Excel. Each row has a unique
identifier and I have 50 columns with different counts for each row. I need to know which rows have counts in more than one column and how many columns have a count in per row. i.e. For Row 1 I would want to report a value of 1 as there is a value in only one column, Row 2 I would want to report a value of 2 and Row 3 I would want to report a value of 3. With 50 columns I need an easy way!!! Column A Column B Column C Row 1 6 0 0 Row 2 1 0 3 Row 3 4 1 1 |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
If you had 4 columns to look at you would place this in the 5 th column ie E1
=COUNTIF(A1:D1,"0") Adapt to suit your no of columns stew "Andy" wrote: I have exported an Access crosstab query into Excel. Each row has a unique identifier and I have 50 columns with different counts for each row. I need to know which rows have counts in more than one column and how many columns have a count in per row. i.e. For Row 1 I would want to report a value of 1 as there is a value in only one column, Row 2 I would want to report a value of 2 and Row 3 I would want to report a value of 3. With 50 columns I need an easy way!!! Column A Column B Column C Row 1 6 0 0 Row 2 1 0 3 Row 3 4 1 1 |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Andy,
Put this in c1 and drag down =COUNT(IF(A1:D1<0,A1:D1)) 'This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "Andy" wrote: I have exported an Access crosstab query into Excel. Each row has a unique identifier and I have 50 columns with different counts for each row. I need to know which rows have counts in more than one column and how many columns have a count in per row. i.e. For Row 1 I would want to report a value of 1 as there is a value in only one column, Row 2 I would want to report a value of 2 and Row 3 I would want to report a value of 3. With 50 columns I need an easy way!!! Column A Column B Column C Row 1 6 0 0 Row 2 1 0 3 Row 3 4 1 1 |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I meant put this in D1 and drag down
=COUNT(IF(A1:C1<0,A1:C1)) 'This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "Mike H" wrote: Andy, Put this in c1 and drag down =COUNT(IF(A1:D1<0,A1:D1)) 'This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "Andy" wrote: I have exported an Access crosstab query into Excel. Each row has a unique identifier and I have 50 columns with different counts for each row. I need to know which rows have counts in more than one column and how many columns have a count in per row. i.e. For Row 1 I would want to report a value of 1 as there is a value in only one column, Row 2 I would want to report a value of 2 and Row 3 I would want to report a value of 3. With 50 columns I need an easy way!!! Column A Column B Column C Row 1 6 0 0 Row 2 1 0 3 Row 3 4 1 1 |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
If your data was over 4 columns then this should go in E1
=COUNTIF(A1:D1,"0") Adapt to suit your no of Columns Best Stew "Andy" wrote: I have exported an Access crosstab query into Excel. Each row has a unique identifier and I have 50 columns with different counts for each row. I need to know which rows have counts in more than one column and how many columns have a count in per row. i.e. For Row 1 I would want to report a value of 1 as there is a value in only one column, Row 2 I would want to report a value of 2 and Row 3 I would want to report a value of 3. With 50 columns I need an easy way!!! Column A Column B Column C Row 1 6 0 0 Row 2 1 0 3 Row 3 4 1 1 |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
simple really! Cheers
"stew" wrote: If your data was over 4 columns then this should go in E1 =COUNTIF(A1:D1,"0") Adapt to suit your no of Columns Best Stew "Andy" wrote: I have exported an Access crosstab query into Excel. Each row has a unique identifier and I have 50 columns with different counts for each row. I need to know which rows have counts in more than one column and how many columns have a count in per row. i.e. For Row 1 I would want to report a value of 1 as there is a value in only one column, Row 2 I would want to report a value of 2 and Row 3 I would want to report a value of 3. With 50 columns I need an easy way!!! Column A Column B Column C Row 1 6 0 0 Row 2 1 0 3 Row 3 4 1 1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How to count for 3 columns | Excel Worksheet Functions | |||
Count in two columns | Excel Worksheet Functions | |||
I would like to Count values in two columns. | Excel Worksheet Functions | |||
count conditionally across two columns? | Excel Worksheet Functions | |||
Count certain changes between columns | Excel Worksheet Functions |