Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting x instances of a string across columns...
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting the number of instances of a string within another string | Excel Worksheet Functions | |||
Counting instances that 2 different columns match | Excel Worksheet Functions | |||
Countif function for instances of text string contained | Excel Worksheet Functions | |||
Countif function for instances of text string contained | Excel Worksheet Functions | |||
Counting instances in a cell | Excel Discussion (Misc queries) |