Search code examples
if-statementgoogle-sheetssumgoogle-sheets-formulaarray-formulas

Why is ArrayFormula causing empty value "" to register > 0

Sample sheet link: https://docs.google.com/spreadsheets/d/16huMKzeqbg3gho5klvRj91878H1ZARIs4F9RVRwE9M0

Tab: Week 45-48

I have this arrayformula set to pull $ amount data from another tab:

Cell F42:

=IFERROR(IF(value(F4) = value(Settings!$B$26),ArrayFormula(INDEX(FullReport!$A:$A,SMALL(IF("Labour Cost"=FullReport!$A:$A,ROW(FullReport!$A:$A)+1),1))),""),0)

It leaves a blank field if the value is empty. I have another formula set to read this data if the value is greater than $0.00

Cell D38:

=SUM(((IF(C42 > 0, C42,C41)) + (IF(D42 > 0, D42,D41)) + (IF(E42 > 0, E42,E41)) + (IF(F42 > 0, F42,F41)) + (IF(G42 > 0, G42,G41)) + (IF(H42 > 0, H42,H41)) + (IF(I42 > 0, I42,I41))))+B39

Unfortunately it seems the blank field "" being generated in cell F42 is causing cell D38 to register as a value greater than $0.00 and is throwing the expected results off.

Looking for a way around this, thanks for any assistance.

Solution

  • if you run =IF(F42 > 0, F42, F41) the output is blank cell so all the concerns about arrayformula should be discarded.

    on the other hand, there is =IF(D42 > 0, D42, D41) which results in FALSE so in your SUM there are summed 4 values: B39 + C41 + D41 + E41 resulting in $5,571.08 (see the red table)

    0

    if you expect the sum to exclude D41 value your IF formula should be:

    =IF((D42=0)+(D42=""), D42, D41)

    the + sign acts as OR

    or you can just use:

    =ARRAYFORMULA(SUMPRODUCT(IF(C42:I42*1>0, C41:I41, C42:I42)))+B39

    0