Thread
:
counting x instances of a string across columns...
View Single Post
#
7
Posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
external usenet poster
Posts: 5,651
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
Reply With Quote
Ron Rosenfeld
View Public Profile
Find all posts by Ron Rosenfeld