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

If you had 4 columns to look at you would place this in the 5 th column ie E1
=COUNTIF(A11,">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

Andy,

Put this in c1 and drag down

=COUNT(IF(A11<>0,A11))

'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

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(A11<>0,A11))
>
> '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

If your data was over 4 columns then this should go in E1
=COUNTIF(A11,">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

simple really! Cheers

"stew" wrote:

> If your data was over 4 columns then this should go in E1
> =COUNTIF(A11,">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