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 <ctrl+shift+enter>.

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
Ignoring Blank Cells Containing Invisible Spaces when Using Array Formulas

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.