Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF with multiple conditions?
(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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF with multiple conditions?
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. "Eric" wrote: (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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF with multiple conditions?
Just do: =IF(COUNTIF([range],"X")=1,"only one X","other")
Dave -- Brevity is the soul of wit. "Barb Reinhardt" wrote: 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. "Eric" wrote: (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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF with multiple conditions?
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. -- Regards Roger Govier "Eric" wrote in message ... (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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF with multiple conditions?
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" "Eric" wrote: (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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF with multiple conditions?
Very nice Luc!
-- Regards Roger Govier "PapaDos" wrote in message ... 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" "Eric" wrote: (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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF with multiple conditions?
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. "PapaDos" wrote: 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" "Eric" wrote: (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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF with multiple conditions?
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. -- Regards, Luc. "Festina Lente" "Eric" wrote: 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. "PapaDos" wrote: 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" "Eric" wrote: (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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif & Sumif with Multiple criteria | Excel Worksheet Functions | |||
CountIf with multiple conditions | Excel Worksheet Functions | |||
COUNTIF and multiple conditions | Excel Worksheet Functions | |||
How to multiple conditions to validate more than 2 conditions to . | Excel Worksheet Functions | |||
COUNTIF using multiple conditions? | New Users to Excel |