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.
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.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks