| Forum Home | ||||
| PC World Chat | ||||
| Thread ID: 56527 | 2005-04-07 16:57:00 | Excel COUNTIF formula help required | Suz8175 (7828) | PC World Chat |
| Post ID | Timestamp | Content | User | ||
| 342537 | 2005-04-07 16:57:00 | Hi I have struggled for days to have Excel count 2 conditions. All I want is the number of issues if: The data in column A contains the word Cab (it may be in a cell containing other words) AND the information in Column B is equal to yes. I can get the formula working if I do an array formula, but it only works where the value of column A="Cab" but I need it to represent A="*Cab*" if you know what I mean. The Column B part of the formula is ok. Any ideas? Thanks Suz |
Suz8175 (7828) | ||
| 342538 | 2005-04-07 17:27:00 | Please post the formula as you have written it. i.e =COUNTIF(a1,"*cab*") | Steven (7085) | ||
| 342539 | 2005-04-07 20:47:00 | Steven will do, the formulas I have used are on my desk so I'll post them in the morning. Thanks Suzanne |
Suz8175 (7828) | ||
| 342540 | 2005-04-08 08:10:00 | Here's some of the formulas I have tried so far: IF(AND(A2:A5="yes",B2:B5="cab"),1) IF(AND(A2="yes",B2="cab"),1) COUNTIF(AND(A2="yes",B2="cab")) SUM((A2:A5="yes")*(B2:B5="cab")) using CTL, SHiFT and Enter COUNT(IF((A2:5="yes")*(B2:B5="cab")) using CTL SHIFT and Enter Most of these work for the range where cab appears in a cell on is own, but there are a couple of cells which have a string of text: cab/Bdy/shl and I need these cells picked up in the count. Thanks Suzanne |
Suz8175 (7828) | ||
| 342541 | 2005-04-08 08:55:00 | Hi Suzanne If you enter this formula in Cell C1 =COUNTIF(A1,"*cab*")=COUNTIF(B1,"yes") It will give you the answer "True" in cell C1 if in cell A1 you have "cab" in any form (cabin, cab, in a cab,abcabdef, etc...) and in cell B2 you have "yes". If not it will return "False". |
Steven (7085) | ||
| 342542 | 2005-04-08 09:05:00 | Or this formula if you would like the number "1" returned for true and "0" for false =COUNTIF(A1,"*cab*")*COUNTIF(B1,"yes") |
Steven (7085) | ||
| 342543 | 2005-04-08 13:34:00 | Steven Thank you. Those formulaes didn't take well to using data in a range, so I have added a column to my spreadsheet and a simple SUM calculation at the end of it. Thanks again Suzanne :) |
Suz8175 (7828) | ||
| 1 | |||||