COUNTIF with multiple conditions?

E

Eric

(Note: I also posted this on Excel / General questions. I hope I'm not
violating a rule by double-posting.)

I've got a table A2:Z100, with X's in some of the cells. A row may have one
or multiple Xs. I'm trying to find the number of rows which are "pureplays",
which is defined as a row with only one X.

For each column, I'd like to count the number of pureplays for that column
only, and put that value in the top row. So, for example, C1 would count the
number rows that have contain one X only and that X is in column C.

(I can't figure out how to do it with COUNTIF. A formula
COUNTIF(C2:C100,"X") will count the number of Xs in column C, but it's not
limited to the rows containing only one X.)

Any ideas??

Thanks.
 
B

Barb Reinhardt

This looks like a sumproduct question to me, but I'm not sure it can handle
26 different conditions.

=SUMPRODUCT(--(A1:A100="X"),--(B1:B100="X"),--(C1:C100="X")) would give you
the number of rows that only have X in them in columns A-C.
 
R

Roger Govier

Hi Eric

You could do it with two additional columns AA and AB
in AA2
=IF(COUNTIF(A2:Z2,"X")=1,1,"")
in AB2
=IF(AA2=1,CHAR(MATCH("x",A2:Z2)+64),"")

Copy AA2:AB2 through AA3;AA100
You will now have a column of data where the column letter containing
only a single X in the row is shown.
 
P

PapaDos

Try this for column A:

=SUMPRODUCT( ( A$2:A$100 = "x" ) * ( COUNTIF( OFFSET( $A$2:$Z$2, ROW(
A$2:A$100 ) - 2, 0 ), "x" ) = 1 ) )

Drag-fill as needed.
 
R

Roger Govier

Very nice Luc!

--
Regards

Roger Govier


PapaDos said:
Try this for column A:

=SUMPRODUCT( ( A$2:A$100 = "x" ) * ( COUNTIF( OFFSET( $A$2:$Z$2, ROW(
A$2:A$100 ) - 2, 0 ), "x" ) = 1 ) )

Drag-fill as needed.

--
Regards,
Luc.

"Festina Lente"
 
E

Eric

Thanks. Clever. I didn't know that a logical value works as a 0/1 in
multiplication.

Although I'm new to SUMPRODUCT, I think that your formula needs a little
adjustment. Either replace the asterisk with a comma (to separate the two
arguments of SUMPRODUCT), or use simply SUM instead. Does that seem right to
you?

Thanks for your help.
 
P

PapaDos

No.

But don't take my word for it, try and play with your ideas/solutions...

There is more than one way to do this, using SUMPRODUCT, SUM, COUNT, etc.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top