I have this big LET
=LET(paper; $A5; sellDate; $M5; qtySold; $B5;initQty; IFERROR(XLOOKUP($A5; $A$50:$A$80; $B$50:$B$80); 0);initCost; IFERROR(XLOOKUP($A5; $A$50:$A$80; $D$50:$D$80); 0);buyQty; SUMIFS($B$5:$B$45; $A$5:$A$45;paper; $F$5:$F$45;"<"&sellDate); sellQtyBefore; SUMIFS($B$5:$B$45; $A$5:$A$45; paper; $M$5:$M$45; "<"&$M5); costBefore; initCost + SUMIFS($E$5:$E$45; $A$5:$A$45; $A5; $F$5:$F$45; "<"&$M5); qtyBefore; initQty + buyQty - sellQtyBefore; avgPrice; IF(qtyBefore=0; 0; costBefore / qtyBefore); IF($F5=""; N5 - qtySold * avgPrice; IF(qtyBefore < 0; avgPrice*qtySold - $E5;0)))
When i try to calculate it it gives the #VALUE! error. I check with the function wizard and the first error appears in the calculation of SUMIFS($B$5:$B$45; $A$5:$A$45;paper; $F$5:$F$45;"<"&sellDate). It gives an #NAME! error. If I try to calculate =SUMIFS($B$5:$B$45; $A$5:$A$45;$A5; $F$5:$F$45;"<"&$M5) in another cell it works. I do not understand what is the problem with the calculation in the LET. I would appreciate any help.
Please help us to help you…and attach an exsample.ods with concrete data!
Missing dates in column F.
You mean it works in any cell but but it does not work inside the LET function?
.
If there is no date in column F, there is an error in column V. Enter a date in F and the error is gone.
I can put dates in the column F only when a buy happens. But i stil don not understand why the formula works outside the LET function but not inside it. Thanks.
The error is most likely triggered in the final LET parameter, the formula which builds the return value. See highlighted part above. There, the function for TRUE (blank date) in the outer IF() performs a numeric calculation from column N (Tipo), and column N does not contain numbers, nor anything that can be sensibly and unambiguously interpreted as a number.
Thank you keme1. That caused the error you said. I corrected it but still get the same error as before with the sumifs formula. Thank you very much.
I think because a wrong column is reference in the LET result, except in the first formula:
N5: ······IF($F5=""; $J5 - qtySold * avgPrice; IF(qtyBefore < 0; avgPriceqtySold - $E5;0))
N6: ······IF($F6=""; N6 - qtySold * avgPrice; IF(qtyBefore < 0; avgPriceqtySold - $E6;0))
Column N has strings not numbers, when column F is empty produce the error, using column N in calculations.