Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif cell if other cells match criteria
I have been playing around with the countif statements and am trying to
figure out a few forumlas which I can not see to code: Arguments: Checking the cells c6:f6 if any of the cells have the words "Missing" "Broken" "* Missing" "No Power" "Liscense" "Needs to be reflashed" "Not on Network" cell is 0, if not then cell is 1 Count the cells from c6:c7 if the cell is blank and (e6:e7 or f6:f7 does not say "Not on Network") Count the cells from e6:e7 if cell has the word ("Missing" or "Broken") and (e6:e7 or f6:f7 does not say "Not on Network") Count the cells from d6:d7 if cell has the word "PS 2" and (e6:e7 or f6:f7 does not say "Not on Network") *I think that this could just be the same formula for the one where it checks for blank, but if I am wrong I added this* Any help would be appreciated For the real table the ranges are much larger, I just put them as small numbers for less typing. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif cell if other cells match criteria
For this part:
Checking the cells c6:f6 if any of the cells have the words "Missing" "Broken" "* Missing" "No Power" "Liscense" "Needs to be reflashed" "Not on Network" cell is 0, if not then cell is 1 Something like this in say, G6, should work: =IF(SUMPRODUCT(--(ISNUMBER(SEARCH({"Missing ";"Broken";"No Power";"License";"Needs to be reflashed";"Not on Network"},C6:F6))))0,1,0) Above construct disregards this textstring: * Missing which seems to me to be a duplicate reqt (Think you ask way too many questions in one post, it's intimidating. Keep it to one question per post) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif cell if other cells match criteria
Thanks.
The reason why I had the "* Missing" is because there are two different things that can be "USB Missing" or "PS2 Missing", but I can just add those in to that formula. Thanks again "Max" wrote: For this part: Checking the cells c6:f6 if any of the cells have the words "Missing" "Broken" "* Missing" "No Power" "Liscense" "Needs to be reflashed" "Not on Network" cell is 0, if not then cell is 1 Something like this in say, G6, should work: =IF(SUMPRODUCT(--(ISNUMBER(SEARCH({"Missing ";"Broken";"No Power";"License";"Needs to be reflashed";"Not on Network"},C6:F6))))0,1,0) Above construct disregards this textstring: * Missing which seems to me to be a duplicate reqt (Think you ask way too many questions in one post, it's intimidating. Keep it to one question per post) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif cell if other cells match criteria
Welcome. Yes, just amend to suit.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "FormulaQuestioner" wrote in message ... Thanks. The reason why I had the "* Missing" is because there are two different things that can be "USB Missing" or "PS2 Missing", but I can just add those in to that formula. Thanks again |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting cells that match 2 criteria | Excel Discussion (Misc queries) | |||
Countif - change color of cells that meet criteria | Excel Discussion (Misc queries) | |||
Fill cells if adjacent cell match criteria | Excel Discussion (Misc queries) | |||
countif = < AND value in adjacent columns match criteria | Excel Worksheet Functions | |||
counting cells (COUNTIF) based on two or more criteria | Excel Worksheet Functions |