I’m trying to make a formula-heavy worksheet more efficient by not repeating itself. I have circular formulas where I use a SUMIF(S) value, but only if its non-zero. So they are written as such:
=IF(SUMIF(Table1[ACCOUNT],[@ACCOUNT],Table1[ACTUAL_VAL])<>0,SUMIF(Table1[ACCOUNT],[@ACCOUNT],Table1[ACTUAL_VAL]),"")
So essentially I’m having the formula SUMIF(X) and if not 0, then SUMIF(X). It’s the same work twice within that one formula. Is there anyway to streamline that to save on resources?
I suppose I could create custom functions in VBA, but the amount of times and variations I’d need for that seems excessive.
>Solution :
There is a duplicate out there but I could not find it.
use the reciprocal of the reciprocal:
=IFERROR(1/(1/SUMIF(Table1[ACCOUNT],[@ACCOUNT],Table1[ACTUAL_VAL])),"")
With Office 365 we can use LET:
=LET(x,SUMIF(Table1[ACCOUNT],[@ACCOUNT],Table1[ACTUAL_VAL]),IF(x=0,"",x))
As @GSerg stated(sorry was typing when saw your comment)