Countif cell if other cells match criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

FormulaQuestioner - 09 Feb 2008 04:40 GMT

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.

Reply to this Message

Max - 09 Feb 2008 11:54 GMT

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)

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

FormulaQuestioner - 09 Feb 2008 20:32 GMT

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

> For this part:
> > Checking the cells c6:f6 if any of the cells have the words "Missing"
[quoted text clipped - 10 lines]
> (Think you ask way too many questions in one post, it's intimidating.
> Keep it to one question per post)

Reply to this Message

Max - 10 Feb 2008 00:16 GMT

Welcome. Yes, just amend to suit.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Thanks.
>
[quoted text clipped - 4 lines]
>
> Thanks again

Reply to this Message