[EMAIL PROTECTED] wrote:
On Thursday, October 05, 2006 6:38 PM [GMT+1=CET], [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:
I am trying to do something that doesn't seem all that difficult, but
I can't figure it out.
I have two columns, let's say column A and column B. Cells in Column
A contain a single letter, "A", "B", or "C". Cells in column B also
contain a single letter, "Y" or "N". There are several hundred rows
of data.
I know I can use COUNTIF to count all of the "Y's" in column B. What
I would like to do is count all of the instances where Column A is an
"A" and Column B is "Y". (Then do the same for the other combinations
of letters, but I do the extrapolation once I figure out how to do it
with "A" and "Y".)
I can think of some in-elegant ways of doing it, like sorting by
column A, putting all of the rows with "A" into a separate sheet,
etc., but I'd like to avoid that level of data manipulation if I
could.
Any suggestions would be apprectiated; thanks.
Lets assume you have 350 rows; this number only gets used in step 2
In two steps using an intermediate column:
1. In cell C1 put the formula =AND(A1="A";B1="Y")
Copy this down to row 350. This will put TRUE or FALSE into each cell
in column C
2. In cell D1 put the formula =COUNTIF(C1:C350;TRUE)
D1 will contain the appropriate count.
At the moment I can't see how to do it without the intermediate
column, but I'm sure someone will show us.
At the risk of teaching my grandmother to suck eggs, "copy this down
..." is achieved as follows:
1. Select cell C1
2. Click on the little black square that appears just outside the
cell's bottom right corner 3. While holding the mouse button down,
drag the cursor (which has changed to a cross) as far as necessary
(row 350 in our example)
Harold Fuchs
London, England
Thanks for the response. I did think of trying an approach like you
suggest. It would be awkward to do in my real situation, because I
oversimplified things in my description, in an effort not to get too
bogged down in details.
What I actually have is columns A and B like I described, but also many
more columns (like 40 more), all of which contain Y's and N's (this is
an employee satisfaction survey that I volunteered to analyze). Also, in
addition to counting the Y's, I need to count the N's (and since not
every person returning the survey gives a response to each question, I
do have to count both of them, I can't assume that the number of N's is
equal to the number of surveys minus the number of Y's).
So to do this approach, that you suggested, I'd need to insert an extra
two columns between each of the other columns (one to count the Y's and
one to count the N's), and put in the "AND" formula as you described.
Certainly possible, but I am still hoping I can find a way to do this
all in one step.
Anyone else have any suggestions? I keep feeling that there has to be a
fairly direct way that I'm missing.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
