|

# » Ignoring Blank Cells Containing Invisible Spaces when Using Array Formulas

Problem:

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 and simultaneously 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)
Screenshot // Ignoring Blank Cells Containing Invisible Spaces when Using Array Formulas

Rate This Tip
 1 2 3 4 5
Rating: 2.64     Views: 34767
No comments have been submitted.
Name
Comment Title
Comments