[SOLVED] Google Sheets - How to sum an operation involving multiple colums if one column is not empty?

Google Sheets - How to sum an operation involving multiple colums if one column is not empty?

I have a simple dataset with 2 columns :

Column A contains a "Value", but that "Value" can be empty.
Column B contains an "Average", never empty.

I need a formula to calculate a sum over all rows (rows noted "i") : SUM(if Ai not empty, Ai-Bi).

I have tried fiddling with SUMIF(), but I don't know how to ask for an operation involving multiple columns using ranges, after checking if Ai<>"".

[Edit for clarification :]
What I want is : = IF(A1<>"", A1-B1) + IF(A2<>"", A2-B2) + ...
But without manually copy-pasting for each row !

Solution

The reason why you're having problems with SUMIF is because SUMIF requires first/third parameters to be ranges, so you can't perform any operations, such as A:A-B:B, without it no longer being a range.

You can still use SUMIF by taking the conditional sum of each column and subtracting one from the other.

=SUMIF(A:A,"<>")-SUMIF(A:A,"<>",B:B)

However, I think it's a good example to use SUMPRODUCT. Think of your SUMPRODUCT parameters like you would with your FILTER parameters. Use the first parameter as the values you want to sum. The remaining parameters will be your conditionals, returning either TRUE/FALSE or 1/0.

=SUMPRODUCT(A:A - B:B, A:A<>"")

You can achieve the same result using SUM and FILTER.

=SUM(FILTER(A:A - B:B, A:A<>""))