I've set up a table to check that a number in cell A1 is the same as the sum of all cells A3 to A10. If that it so, then A2 says "OK". If A1 <> SUM(A3:A10), then A2 says "ERR".

So in A2, I have the formula:

=IF(A1=SUM(A3:A10),"OK","ERR")

So far, so good.

However, it also says OK if row A1 and A3 to A10 are empty, i.e. those cells haven't been filled in, which I want to avoid because it's a long table, and far too easy to have missed a row out.

So I've been looking at ways of checking whether cells are empty, etc, and there are functions such as ISBLANK, but I can't seem to get that checking for a range of cells.

So, how would you do it?

Hi mate,

Well, you can try nesting a double test:

=IF(A1=SUM(A3:A5), IF(AND(A1<>"0", SUM(A3:A5)=0), "ERR", "OK"), "ERR")

Because SUM will always return "0", even with empty cells, you risk the check coming out trumps if the sum and the check total are both empty (or 0).

One way of working this kink out, is to further check that the sum of empties (0) is different from the check total (A1), hence nesting the IF above and testing that A1 is NOT 0 when the sum is.

Hope I've not missed anything.

You can also try for ISNUMBER, etc but these don't work well on a range. The other (laborious) alternative, is to have a parallel (hidden) column with control values. Say you have A3:A10 and also B3:B10. Column B will hold control values. For each row, you enter B3 = ISNUMBER(A3). This will place a TRUE/FALSE against each value.

Then, you can test if AND(B3:B10) is TRUE. If any cell in A3:A10 is left empty, AND(B3:B10) will return FALSE. If all cells are populated, it'll return TRUE.

From there it is easy to incorporate AND(B3:B10) into the check to ensure you haven't missed any value.

Hope that makes sense. Let me know if that helps

Puma

Worked a treat, thanks!

Also good in that you can make the distinction between what hasn't been filled in and what's incorrect

Glad I could be of help