Google Sheets ARRAYFORUMLA that can show 0 if the value is less than or equal to zero. Otherwise show the value of that set of data

Question

I have set of columns that I am attempting to calculate the combined total of those columns then subtract from that total 8, if the difference after 8 is equal to or less than 0 I want to only show zero in the column I am doing this formula in. For those who might ask, I am using the ARRAYFORUMLA cause I want this calculation to repeat as I add new rows, keeping the totals I am seeking on the same row as the calculation is being done onto. So far I have most of this working. Well up to the IF ELSE THEN type of portion. My attempt is/was

=if(LTE((B3:B)+(C3:C)-8,0),ARRAYFORMULA((B3:B)+(C3:C)), 0)

Answer 1

As long as I'm understanding you correctly, I believe this will work:

=if(lte(sum(B2:C)-8,0),0,sum(B2:C))

It's at the top of the row and sums columns B and C. You can add more columns easily this way by either changing B/C to something else or passing more columns in.

Sum-8 is greater than 0

Sum-8 is less than 0

Answer 2

Short answer

ARRAYFORMULA that can show 0 if the value is less than or equal to zero. Otherwise show the value of that set of data:

=ArrayFormula(IF(LTE(A1:A3,0),0,A1:A3))

Explanation

The basic IF syntax requires the use of scalar values, to use it with arrays, ARRAYFORMULA is required as an outer function:

ARRAYFORMULA(IF(array_logical_expression,array_if_true,array_if_false))

For the specific case described on the body of the question, the corresponding formula is:

=ARRAYFORMULA(IF(LTE(B3:B+C3:C-8,0),0,B3:B+C3:C))

Reference

Google Docs editors Help

• IF
• ARRAYFORMULA
• Using arrays in Google Sheets

Answer 3

If what you want to do is to add each row to the total but only if its sum is 8 or greater, then your original formula was nearly OK but the two parts of the IF statement should have been reversed

=sum(ArrayFormula(if(LTE((B3:B)+(C3:C)-8,0),0,(B3:B)+(C3:C))))

You could also take most of the brackets out

=sum(ArrayFormula(if(LTE(B3:B+C3:C-8,0),0,B3:B+C3:C)))

and this would also work

=sum(ArrayFormula(if(LTE(B3:B+C3:C,8),0,B3:B+C3:C)))