Suppose I have the following expression repeated in a long column:

=IF(OR($U2=""; COUNT(aHugeRange)=0); ""; SUM(aHugeRange))

I would like to avoid performing COUNT() and SUM() as much as possible to increase performance.

Will COUNT(), SUM() be ever executed for the rows where $U is empty?

Will the following modification work differently?

=IF($U2=""; ""; IF(COUNT(aHugeRange)=0; ""; SUM(aHugeRange)))
  • 1
    Down-voter, please provide a comment, what is wrong? Mar 21 '19 at 14:15

My test showed that OR and AND are fully evaluated, but IF is short-circuit evaluated:

Result   Formula               Notes
FALSE    =IF( FALSE; 1/0=1/0)  no error so I think 1/0=1/0 is not evaluated 
#DIV/0!  =AND(FALSE; 1/0=1/0)  error, so 1/0=1/0 is certainly evaluated
#DIV/0!  =OR( TRUE;  1/0=1/0)  same

So I should use IF where performance is important.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.