Nested IF Statement not working

[snoozee]

Hi

I've read through all the posts I can find however I can't seem to find one that helps me work out where i'm going wrong.

This is what i currently have =IF(B2:B8="Y",char(80),IF(AND(D3<$A$1,B2:B8<>"Y"),CHAR(39),CHAR(137)))

Here's what i'm trying to achieve

If ALL cells in the B2:B8 range contain a "Y" and the date (D3) is less than today's date(A1) then CHAR (80), if ANY cells in the range don't ="Y" then CHAR(39) otherwise CHAR(137)

The current statement is only looking at B2 and then returning the desired result so i'm at a loss as to what i'm doing wrong

 

[JonXL]

countif(b2:b8,"y")=7

countif(b2:b8,"y")<>7

 

[Peter_SSs]

Here's what i'm trying to achieve

If ALL cells in the B2:B8 range contain a "Y" and the date (D3) is less than today's date(A1) then CHAR (80), if ANY cells in the range don't ="Y" then CHAR(39) otherwise CHAR(137)

Does this do what you want? If not, further examples & explanation please.

=IF(COUNTIF(B2:B8,"Y")=ROWS(B2:B8),IF(D3<$A1,CHAR(80),CHAR(137)),CHAR(39))

 

[snoozee]

Hi Peter_SS thanks for the prompt reply however it doesn’t work

i’ve decided to simplify this a bit

I need it to look in all cells within the B2:B8 range and if ALL cells are “Y” then Char(80), if ALL cells within this range don’t contain “Y” then Char(137)

 

[snoozee]

Thanks JonXL this worked perfectly

 

[snoozee]

JonXL - I decided to add a third condition when the cell is "blank" however this doesn't appear to want to work

=IF(COUNTIF(G8:G13,"Y")=6,CHAR(80),IF(COUNTIF(G8:G13,"Y")<>6,CHAR(209),IF(COUNTIF(G8:G13,"")=6,CHAR(144))))

The first two work perfectly.

 

[snoozee]

Fixed it as wasn't till I looked at it again that i realized i had the last two functions around the wrong way, thanks heaps for your help

 

[JonXL]

Fixed it as wasn't till I looked at it again that i realized i had the last two functions around the wrong way, thanks heaps for your help

You are welcome.

A final note: you can simplify the COUNTIF that checks for all empty cells by using COUNTA, which counts only filled cells and thus doesn't require the second "" argument of the COUNTIF:

COUNTA(G8:G13)=0

 

[snoozee]

Thanks JonXL