0

Excel is not recognizing this LAMBDA/LET combo formula as a formula (receiving the 'there's a problem with this formula' error). I thought it might have been something to do with the variable names I was using, but I've changed them and that doesn't seem to have fixed it. I think another possibility is that I have a typo somewhere but I've poured over it and I can't find it.

The error moves the cursor to 'f_R,data!$T:$T,' when the error is thrown, which I think implies the error occurs here, but when I remove this portion from the entire formula, the error is still thrown and just points to the next name/value pair in the LET function.

=LAMBDA(d,f_1,v_1,[f_2],[v_2],[f_3],[v_3],[f_4],[v_4],

LET(

dR,INDIRECT("data!"&REPLACE(ADDRESS(1,MATCH(d,data!$4:$4,0),4),FIND(1,ADDRESS(1,MATCH(d,data!$4:$4,0),4)),1,"")&":"&REPLACE(ADDRESS(1,MATCH(d,data!$4:$4,0),4),FIND(1,ADDRESS(1,MATCH(d,data!$4:$4,0),4)),1,"")),
f_R,data!$T:$T,
c1,INDIRECT("data!"&LEFT(ADDRESS(1,MATCH(f_1,detail[#Headers],0)),(LEN(ADDRESS(1,MATCH(f_1,detail[#Headers],0)))-2))&":"&LEFT(ADDRESS(1,MATCH(f_1,detail[#Headers],0)),(LEN(ADDRESS(1,MATCH(f_1,detail[#Headers],0)))-2))),
c2,INDIRECT("data!"&LEFT(ADDRESS(1,MATCH(f_2,detail[#Headers],0)),(LEN(ADDRESS(1,MATCH(f_2,detail[#Headers],0)))-2))&":"&LEFT(ADDRESS(1,MATCH(f_2,detail[#Headers],0)),(LEN(ADDRESS(1,MATCH(f_2,detail[#Headers],0)))-2))),
c3,INDIRECT("data!"&LEFT(ADDRESS(1,MATCH(f_3,detail[#Headers],0)),(LEN(ADDRESS(1,MATCH(f_3,detail[#Headers],0)))-2))&":"&LEFT(ADDRESS(1,MATCH(f_3,detail[#Headers],0)),(LEN(ADDRESS(1,MATCH(f_3,detail[#Headers],0)))-2))),
c4,INDIRECT("data!"&LEFT(ADDRESS(1,MATCH(f_4,detail[#Headers],0)),(LEN(ADDRESS(1,MATCH(f_4,detail[#Headers],0)))-2))&":"&LEFT(ADDRESS(1,MATCH(f_4,detail[#Headers],0)),(LEN(ADDRESS(1,MATCH(f_4,detail[#Headers],0)))-2))),

IFS(

ISOMITTED(2f),SUMIFS(dR,dR,1,f_R,"F",c1,v_1),
ISOMITTED(3f),SUMIFS(dR,dR,1,f_R,"F",c1,v_1,c2,v_2),
ISOMITTED(4f),SUMIFS(dR,dR,1,f_R,"F",c1,v_1,c2,v_2,c3,v_3),
TRUE,SUMIFS(dR,dR,1,f_R,"F",c1,v_1,c2,v_2,c3,v_3,c4,v_4))))

(DATE(2022,7,31),"Home Department Description","*","Home Department Description","*","Home Department Description","*")
PeterT
  • 8,232
  • 1
  • 17
  • 38
Gavin
  • 21
  • 3
  • 1
    Have you tried putting it into the Advanced Formula Environment https://www.microsoft.com/en-us/garage/profiles/advanced-formula-environment-a-microsoft-garage-project/ ? It flagged up quite a few errors when I tried pasting your formula in. – Tom Sharpe Sep 11 '22 at 19:51
  • 1
    Thanks Tom! I dropped the formula into the advanced formula editor but rolled back by one iteration of what I pasted here, added back the rolled back condition (slowly) and now its working. Not sure where I missed a parens or whatever the issue was here, but going to the formula editor seemed to do the trick - thanks! – Gavin Sep 11 '22 at 20:37

0 Answers0