How to use: Sum if is not blank

0
votes

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.

2
excelexcel-formulaworksheet-functionsumifsnonblank
Why not use a sumif on the x column: =SUMIF(A:A,"<3") - 5202456
This is my example. I mean in cases Y column has blank value is not depend on X column - Luu nguyen

2 Answers

4
votes

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

1
votes

Just FYI, if you created the "Y" column just to help you solve this problem, then you can delete the column altogether, because what you desire is possible with the "X" column only:

= SUMIF(A2:A6,"<=2")