counting x instances of a string across columns...
 

A B C D
Yes No Yes Yes
No No No Yes
No Yes Yes No
Yes Yes No No


What I'm trying to do is count the cases where there is one "Yes" (or
maybe 2 or 3 yeses) in any of the columns, with one formula for the
entire array. So to count rows with one yes, there would be 1. With 3
yeses, there would be 1 row. With 2 yeses, there would be 2 rows.

Can't figure out how to do this without using a helper column, any
help is appreciated..

counting x instances of a string across columns...
 

Unless you have an overwhelming reason not to, I would use a helper column
using COUNTIF and then SUMIF/SUMPRODUCT to sum the results.

" wrote:



A B C D
Yes No Yes Yes
No No No Yes
No Yes Yes No
Yes Yes No No


What I'm trying to do is count the cases where there is one "Yes" (or
maybe 2 or 3 yeses) in any of the columns, with one formula for the
entire array. So to count rows with one yes, there would be 1. With 3
yeses, there would be 1 row. With 2 yeses, there would be 2 rows.

Can't figure out how to do this without using a helper column, any
help is appreciated..

counting x instances of a string across columns...
 

=SUM((A1="yes")+(B1="yes")+(C1="yes")+(D1="yes")) This produces the answer
3 for your first row.
=SUM((A2="yes")+(B2="yes")+(C2="yes")+(D2="yes")) This produces the answer
1 for your second row.
etc
Does this help?

wrote in message
ps.com...


A B C D
Yes No Yes Yes
No No No Yes
No Yes Yes No
Yes Yes No No


What I'm trying to do is count the cases where there is one "Yes" (or
maybe 2 or 3 yeses) in any of the columns, with one formula for the
entire array. So to count rows with one yes, there would be 1. With 3
yeses, there would be 1 row. With 2 yeses, there would be 2 rows.

Can't figure out how to do this without using a helper column, any
help is appreciated..

counting x instances of a string across columns...
 

=SUM((A1="yes")+(B1="yes")+(C1="yes")+(D1="yes")) This produces the answer
3 for your first row.
=SUM((A2="yes")+(B2="yes")+(C2="yes")+(D2="yes")) This produces the answer
1 for your second row.
etc
Does this help?


wrote in message
ps.com...


A B C D
Yes No Yes Yes
No No No Yes
No Yes Yes No
Yes Yes No No


What I'm trying to do is count the cases where there is one "Yes" (or
maybe 2 or 3 yeses) in any of the columns, with one formula for the
entire array. So to count rows with one yes, there would be 1. With 3
yeses, there would be 1 row. With 2 yeses, there would be 2 rows.

Can't figure out how to do this without using a helper column, any
help is appreciated..

counting x instances of a string across columns...
 

=SUM((A1="yes")+(B1="yes")+(C1="yes")+(D1="yes")) This produces the answer
3 for your first row.
=SUM((A2="yes")+(B2="yes")+(C2="yes")+(D2="yes")) This produces the answer
1 for your second row.
etc
Does this help?

wrote in message
ps.com...


A B C D
Yes No Yes Yes
No No No Yes
No Yes Yes No
Yes Yes No No


What I'm trying to do is count the cases where there is one "Yes" (or
maybe 2 or 3 yeses) in any of the columns, with one formula for the
entire array. So to count rows with one yes, there would be 1. With 3
yeses, there would be 1 row. With 2 yeses, there would be 2 rows.

Can't figure out how to do this without using a helper column, any
help is appreciated..

counting x instances of a string across columns...
 

Try this:

=SUMPRODUCT(--(MMULT(--(A1:D4="yes"),{1;1;1;1})=n))

Where n = 1, 2 or 3 (or whatever)

Note: this will work if you have no more than 5460 rows of data.

--
Biff
Microsoft Excel MVP


wrote in message
ps.com...


A B C D
Yes No Yes Yes
No No No Yes
No Yes Yes No
Yes Yes No No


What I'm trying to do is count the cases where there is one "Yes" (or
maybe 2 or 3 yeses) in any of the columns, with one formula for the
entire array. So to count rows with one yes, there would be 1. With 3
yeses, there would be 1 row. With 2 yeses, there would be 2 rows.

Can't figure out how to do this without using a helper column, any
help is appreciated..

counting x instances of a string across columns...
 

On Wed, 18 Jul 2007 17:00:30 -0000, wrote:



A B C D
Yes No Yes Yes
No No No Yes
No Yes Yes No
Yes Yes No No


What I'm trying to do is count the cases where there is one "Yes" (or
maybe 2 or 3 yeses) in any of the columns, with one formula for the
entire array. So to count rows with one yes, there would be 1. With 3
yeses, there would be 1 row. With 2 yeses, there would be 2 rows.

Can't figure out how to do this without using a helper column, any
help is appreciated..


Given your data

Put the number of yes's per row in H1:Hn

Then **array-enter** this formula:

=SUM(--(MMULT(--($A$1:$D$4="Yes"),{1;1;1;1})=H1))

Copy/drag down.

To **array-enter**, hold down <ctrl<shift while hitting <enter. Excel will
place braces {...} around the formula.

The array constant should have the same number of "1's" as there are columns.
--ron

counting x instances of a string across columns...
 

If you're going to go about it that way then you don't need the SUM
function.

As written, you're only summing a single number, the result of:

(A1="yes")+(B1="yes")+(C1="yes")+(D1="yes)

=SUM(3)

So, you can just use:

=(A1="yes")+(B1="yes")+(C1="yes")+(D1="yes")

--
Biff
Microsoft Excel MVP


"Dave Thomas" wrote in message
t...
=SUM((A1="yes")+(B1="yes")+(C1="yes")+(D1="yes")) This produces the
answer
3 for your first row.
=SUM((A2="yes")+(B2="yes")+(C2="yes")+(D2="yes")) This produces the
answer
1 for your second row.
etc
Does this help?

wrote in message
ps.com...


A B C D
Yes No Yes Yes
No No No Yes
No Yes Yes No
Yes Yes No No


What I'm trying to do is count the cases where there is one "Yes" (or
maybe 2 or 3 yeses) in any of the columns, with one formula for the
entire array. So to count rows with one yes, there would be 1. With 3
yeses, there would be 1 row. With 2 yeses, there would be 2 rows.

Can't figure out how to do this without using a helper column, any
help is appreciated..

counting x instances of a string across columns...
 

"Ron Rosenfeld" wrote in message
...
On Wed, 18 Jul 2007 17:00:30 -0000, wrote:



A B C D
Yes No Yes Yes
No No No Yes
No Yes Yes No
Yes Yes No No


What I'm trying to do is count the cases where there is one "Yes" (or
maybe 2 or 3 yeses) in any of the columns, with one formula for the
entire array. So to count rows with one yes, there would be 1. With 3
yeses, there would be 1 row. With 2 yeses, there would be 2 rows.

Can't figure out how to do this without using a helper column, any
help is appreciated..


Given your data

Put the number of yes's per row in H1:Hn

Then **array-enter** this formula:

=SUM(--(MMULT(--($A$1:$D$4="Yes"),{1;1;1;1})=H1))

Copy/drag down.

To **array-enter**, hold down <ctrl<shift while hitting <enter. Excel
will
place braces {...} around the formula.

The array constant should have the same number of "1's" as there are
columns.
--ron

And, if you had 25 (or more) columns in your range you wouldn't necessarily
want to use:

{1;1;1;1;1;1;1;1;1;1;1;1;..............1}

You can generate that vertical array by adding this expression to the
formula:

TRANSPOSE(COLUMN(A1:D4)^0)

Note that TRANSPOSE is an array function and requires array entry. So in
using this you may as well use the SUM version rather than the SUMPRODUCT
version since *both* require array entry.

=SUM(--(MMULT(--(A1:D4="yes"),TRANSPOSE(COLUMN(A1:D4)^0))=n))

--
Biff
Microsoft Excel MVP

counting x instances of a string across columns...
 

On Wed, 18 Jul 2007 17:21:07 -0400, "T. Valko" wrote:

Note that TRANSPOSE is an array function and requires array entry. So in
using this you may as well use the SUM version rather than the SUMPRODUCT
version since *both* require array entry.

=SUM(--(MMULT(--(A1:D4="yes"),TRANSPOSE(COLUMN(A1:D4)^0))=n))

I used the SUM function in my example.
--ron