Excel 2007 : Long IF Statements

**JonathanKilleen** · 12-02-2009, 05:46 AM

Hi, right, i need a complex IF statement which counts up a column, and if the field in the column is blank, it says "Free Seat" into the cell. The probelm is, i cant get any part of the IF statement to work, i dont know where the if statement goes(in the cell of at the bottom of row 23). This is for my A levels, so if anyone can help in anyway, thank you.

Basically, i am trying to make the IF statement look a LITTLE bit like this:

=IF(R4, "", Free)

Now thats obviously not right. Now the bigger problem is, i need to create a formula for the IF statement.. 54x bigger. Like this..

=IF(R4, "", Free+IF(R5, "", Free+IF(R6, "", Free+IF(R7, "", Free+IF(R8, "", Free............+IF(R54, "", Free)))))))))))))))))))))))))))))))))))))))))))))))))))))

If anyone can help me with this equation.. it will be great help.

Thank You Very Much!

**JeanRage** · 12-02-2009, 05:56 AM

Hi,

Most probably the solution lies in a lookup() function ...
Could you post your file for a precise answer ...

HTH

**pike** · 12-02-2009, 05:58 AM

To make it work the syntax would be
=IF(R4,"","Free"&IF(R5,""," Free"))
you may be better off to create a UDF

**teylyn** · 12-02-2009, 06:09 AM

Maybe, if you just need to count how many free seats there are in the column, i.e. how many empty cells in the range A1:A100, use

=SUMPRODUCT(--(ISBLANK(a1:a100)))

adjust ranges to suit your workbook layout.

To put the words "free seat" next to a cell that is blank, use in B1

=if(isblank(A1),"free seat", "")
copy this formula down (adjust the cell reference first!)

Then you could also simply count how often the term "free seat" occurs in column B

=countif(b1:b100,"free seat")

again, adjust ranges to suit

hth