» Ignoring Blank Cells Containing Invisible Spaces when Using Array Formulas
CATEGORY - Excel Formula and Function
VERSION - All Microsoft Excel Versions
Columns A & B contain the number of points scored by a particular player during each quarter of a game.
An empty cell indicates that no points were scored during that quarter.
When using the following Array formula to total the points scored during the 2nd half, #VALUE! is returned.
{=SUM(A2:A5*((B2:B5=3)+(B2:B5=4)))}
Solution 1:
Use the SUM, IF, and ISNUMBER functions as shown in the following Array Formula:
{=SUM(IF(ISNUMBER(A2:A5),(A2:A5)*((B2:B5=3)+(B2:B5=4))))} Note:
It is most likely that the blank cell in column A is actually not empty, but contains an invisible space.
Using the ISNUMBER and IF functions overcomes any errors.
To apply Array formula:
Select the cell, press
Solution 2:
Use the SUMIF function as shown in the following formula:
=SUMIF(B2:B5,3,A2:A5)+SUMIF(B2:B5,4,A2:A5)
Book Store:
Reply: random379
Alan
Hi Chris,
[QUOTE=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[/QUOTE]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.
blank cells or formulas in vba
markk
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?
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