Combination of IF, AND, and COUNTIF; need help

[KL]

Hi, I am trying to make a calculation that will take Pass/Fail values of P or
F and give me an overall value of P or F. There is also an issue if a value
is Not Tested, NT.

For example, I have three levels of requirements (parent, child, and
grandchild). Parent is level 1, Child is level 2, and Grandchild is level 3.

For each grandchild requirement multiple tests can be completed with a P, F,
or NT result. I have come up with a formula that will give me an overall
result of P or F:

=IF(COUNTIF(H8:H9,"F")>0,"F","P") to say If any value in the range is
"F" the overall value is "F". This value would be put in cell H7.

This repeats for the next grandchild requirement that would be in say cells
H10:H12.

Now I can make the same formula for Not Tested as

=IF(COUNTIF(H8:H9,"NT")>0,"NT","") to give me a blank overall result if
there are not tested values.

The problem I am having is how to combine the two formulas. I would like a
formula that says:

If there are any Fs give me an overall F.
If there are any NTs ignore them.
If ALL are Ps give me an overall of P.

I tried to do the following formula but it isn't working and I know I am
missing something I just can't figure out what it is:

=IF(AND(COUNTIF(H8:H10,"F")>0,"F","P"),(COUNTIF(H8:H10,"NT")>0,"NT",""),"P","")

On top of all of that I also need the same type of formula to be in a cell
that can't use a range for the COUNTIF. They would need to be individual
cells.

For example,

Row 1's (parent) calculation needs to be based off of the overall pass/fail
status of rows 2 and 15.
Row 2's (child) calculations needs to be based off of the overall pass/fail
status of rows 3 and 7.
Row 3's (grandchild) calcuations are based off of the cell range.

I tried the following but it didn't work:

=IF(AND(COUNTIF(H3, H7, H10,"F")>0,"F","P"),(COUNTIF(H3, H7,
H10,"NT")>0,"NT",""),"P","")

I know this is a lot in one post but I thought it would be best to get
everything out there.

I would appreciate any and all help with this! Thank you!

 

[Max]

To satisfy this

If there are any Fs give me an overall F.
If there are any NTs ignore them.
If ALL are Ps give me an overall of P.

One way:
=IF(COUNTIF(H8:H10,"F")>0,"F",IF(COUNTIF(H8:H10,"P")=3,"P",""))
Test it out, hit YES below for a thumbs up
The "=3" bit in the check for "P" is the number of cells in the range H8:H10
Note that you can sequence the IF checks to suit as it will be evaluated
from left to right

 

[KL]

Thanks! Max, That formula works great!

=IF(COUNTIF(H8:H10,"F")>0,"F",IF(COUNTIF(H8:H10,"P")=3,"P",""))
Test it out, hit YES below for a thumbs up
The "=3" bit in the check for "P" is the number of cells in the range H8:H10
Note that you can sequence the IF checks to suit as it will be evaluated
from left to right

However, now I need a formula for a group of cells (not in a range) that
does the same thing.

For example,

H5 calculates the same thing but is based on cells H6, H14, and H18.

The cells aren't in a range. Is there a way this can be done. When I try to
replace a cell range in this formula with "H6,H14,H18" I get an error.

Thanks for your help (and anyone help who can help)!

I appreciate it!

Karen

 

[Max]

For non contiguous cells, this is the equivalent of the earlier:
=IF(OR(H6="F",H14="F",H18="F"),"F",IF(AND(H6="P",H14="P",H18="P"),"P",""))

You could also use simple links to "place" all non contiguous cells into a
contiguous range in an empty adjacent part of the sheet, then just use the
earlier COUNTIF checks repointing to the new contiguous range. Or, if the
intervening cells (ie H7:H13 and H15:H17) will not contain anything which
could disturb the required checks, then you could just extend the earlier
range to H6:H18. Joy? hit the YES below

 

[KL]

Max you are awesome! I have to ask was I even close to being on the right
track with the formula I had come up with?

Ok so my next, and I believe last question, is how do I ignore if the cell
is blank?

For example:

H1 would use the formula:
=IF(OR(H6="F",H14="F",H18="F"),"F",IF(AND(H6="P",H14="P",H18="P"),"P",""))

But say H6=P, H14=P, but H18=NT (or blank). Currently the formula gives a
calue of Blank. But I need it to be able to result a P, as the blank would be
ignored.

The same would happen if H6=F, H14=P, and H18=NT (blank). The result would
be H1=F, the blank in H18 would be ignored.

Does that make sense? I do appreciate your help. I am pretty good at
figuring out formulas in excel but I have been at a loss for this one.

Karen

 

[Max]

Maybe this suffices, test it out:
=IF(OR(H6="F",H14="F",H18="F"),"F",IF(OR(H6="P",H14="P",H18="P"),"P",""))

 

[KL]

Max, Thank you so much for your help! I appreciate it immensely. Karen