Microsoft Excel Tip of the day: Using IF and cell ranges

Today I had one of thoose lovely “Ah! Never considered that before!”. I got a question from a colleague that had a formula that looked like this:

=IF((G3&H3&I3&J3&K3&N3&O3&Q3&R3&S3&T3&U3&Y3&Z3&AA3&AB3&AD3&AE3&AH3&AI3&AJ3&AK3)
=””;”Missing”;”Present”)

Excel question

Note that this is no hardcore crazy hackaway thing. Just a common “simple when you think of it”. I’m not claiming to be giving away awesome wisdom here. :)

Apart from not beeing very easy understandable it required a lot of handy work to stay up to date since columns where added periodically.

What it was supposed to do was check if all the cells between G3 and AK3 were empty, write “Missing” if they were and “Present” if they weren’t. If you have the energy to check you will notice that not all cells between G3 and AK3 was included. Some were lost when new columns were added.

Easy! I thougt, and entered: =IF(G3:AK3=””;”Missing”;”Present”)
Unexpected trouble. As far as I can remember I never tried using a range with the IF function that way before, lucky for me since it doesn’t work (neither do named areas).

I figured that I have to calculate a value from these cells so I asked what would be in the cells if present? Unfortunately the answer wasn’t digits. If it were numbers a simple: =IF(SUM(G3:AK3)=0;”Missing”;”Present”) would work.

Still, this should not be too tricky…and it wasn’t. As soon as I stoped thinking on how to identify the cell content at least (yes, that took me some beard scratching minutes).

Instead I count them. This is what I came up with:
IF(COUNTA(G3:AK3=0;”Missing”;”Present”)

=COUNTA simply counts the number of cells with values. Since I specify a range Excel will also update the formula to includedcells created within that range.

As with most questions about Excel, the solution was easy, you just have to think about it first :-)