subtract value in one column from several other columns in google sheets

0
votes

So lets say I have a reference column and I am looking to check the values in several other columns against this reference column. If the values in any of the other columns are greater than the reference value, I'd like to take the delta, for each column , and sum it all up.

In my screenshot below, I am comparing the value for each month column against the reference value. The overflow column is the sum of all the deltas. Will like to replicate that for each row with an array formula if possible.

enter image description here

here is a link to my google sheet if that helps. thanks!!

2
google-sheetsgoogle-sheets-apigoogle-sheets-formulaarray-formulas

2 Answers

0
votes

Revision:

=iferror(sum(filter(B2:L2,B2:L2>A2))-sum(A2*countif(B2:L2,">"&A2)))

You'll have to drag the formula down for this as I'm not sure how to turn it into an array due to the cell comparison restraints.

0
votes

This would do:

=IF(ISNA(ARRAYFORMULA(SUM(FILTER(B2:L2, B2:L2>A2)-A2))), 0, ARRAYFORMULA(SUM(FILTER(B2:L2, B2:L2>A2)-A2)))

ARRAYFORMULA is very useful for this cases.

I'm also using ISNA(), which you can delete and still will work showing #N/A instead of 0, but this will show an error on this cell cause the result is not the expected one.

;)