Search code examples
excelexcel-formulaworksheet-functionsumifsnonblank

How to use: Sum if is not blank

I have an example with 2 columns in excel sheet:

X --- Y

1 --- A

2 --- A

3 ---

5 ---

1 --- A

Column Y was hidden by the formula: =IF(A2<=2,"A","")

I want to sum if column X if Y is not blank. I use this formula: =SUMIF(B:B,"<>",A:A) but it not understand that "" is blank.

I hope somebody can help me.

Solution

  • SUMPRODUCT:

    Use SUMPRODUCT

    =SUMPRODUCT(A2:A6,--(B2:B6<>""))

    SUMIF array:

    Or enter the following as an array formula with Ctrl+Shift+Enter

    =SUM(IF(B2:B6<>"",A2:A6))

    SUMIF with wildcards:

    Using a formula by XOR LX

    =SUM(SUMIF(B2:B6,{"?*",">=0","<0"},A2:A6))

    Data:

    data