IF,AND Formula
 

Trying to get a cell to show a 1 (so I can add it), IF cell A1 is blank and
A2 thru A6 has an "f" in any of them.

If/AND formulas are killing me! Your help is appreaciated.

Try this:

=IF(AND(A1="",COUNTIF(A2:A6,"f")0),1,"")

You didn't say what value you wanted if the 2 conditions are not met so
I assumed you wanted a blank cell. Change the "" before the last
bracket to something else if a blank cell is not what you want.


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=473126

This might help:
=IF(AND(SUMPRODUCT(--(A2:A6="f"))0,ISBLANK(A1)=TRUE),1,0)

"Shinka" wrote:

Trying to get a cell to show a 1 (so I can add it), IF cell A1 is blank and
A2 thru A6 has an "f" in any of them.

If/AND formulas are killing me! Your help is appreaciated.

Thank You! I wrestled with this for 2 days. Nestled functions are kinda
tough. Nice clean solution...thanks again.

"Martin P" wrote:

This might help:
=IF(AND(SUMPRODUCT(--(A2:A6="f"))0,ISBLANK(A1)=TRUE),1,0)

"Shinka" wrote:

Trying to get a cell to show a 1 (so I can add it), IF cell A1 is blank and
A2 thru A6 has an "f" in any of them.

If/AND formulas are killing me! Your help is appreaciated.

That is what I had but couldn't figure out how to nest the AND with the
COUNTIF.

Thanks - both will work.

"Cutter" wrote:


Try this:

=IF(AND(A1="",COUNTIF(A2:A6,"f")0),1,"")

You didn't say what value you wanted if the 2 conditions are not met so
I assumed you wanted a blank cell. Change the "" before the last
bracket to something else if a blank cell is not what you want.


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=473126

You're welcome. Nice to hear that it helped.


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=473126