Thread: Disregarding blank cells containing invisible spaces when using array formulas

Problem:

Columns A:B contain the number of points scored by a certain player during each quarter.
An empty cell indicates no points were scored during that quarter.
When using this array formula to total the points scored during the 2nd half, it returns #VALUE!.


Solution:

It is most likely that the blank cell in column A is actually not empty, but contains an invisible space.
Use the ISNUMBER and IF functions, combined in the following Array Formula to disregard such cells, and thus avoiding errors:
{=SUM(IF(ISNUMBER(A2:A5),(A2:A5)*((B2:B5=3)+(B2:B5=4))))}

(To create Array formula: select the cell after typing the formula,press F2 and then press Ctrl+Shift+Enter)

Example:

Points___Quarter
8________1
5________2
_________3
2________4

Thanks for this tip. I am having trouble adapting it for use with the sumproduct function - can it be adapted so that any rows in the sumproduct array that have blanks or alphas in them are ignored?
Also, is it necessary to use isnumber when using functions average or stdev or is excel smart enough to ignore non-numerics and adjust the total number of rows accordingly?
Thanks
Chris

Hi Chris,

Originally Posted by random379

Thanks for this tip. I am having trouble adapting it for use with the sumproduct function - can it be adapted so that any rows in the sumproduct array that have blanks or alphas in them are ignored?
Also, is it necessary to use isnumber when using functions average or stdev or is excel smart enough to ignore non-numerics and adjust the total number of rows accordingly?
Thanks
Chris

I am not at my PC right now, so I cannot check, but if I recall correctly, the help pages on the AVERAGE function are explicit about how it treats empty cells, zeros, and alphas. Have a look and post back if it doesn't answer your problem.

Alan.

i have a similar problem in that in vba the function "if cell is blank goto" dosen't work because it sees the formula as not a blank. the value is " " which visually is a blank but the formula makes it not a blank.

what can i add to my code to make the function look at the result rather than the contents?