"WorksheetFomulae"
= MAP(lookupRun1, CountMatchesλ(lookupDate1));
= MAP(lookupRun1, SumMatchesλ(lookupDate1));
"Lambda functions"
CountMatchesλ
= LAMBDA(luDate, LAMBDA(run, LET(
dates, SCAN("Date", tblDate, LAMBDA(p,d,IF(d>0,d,p))),
selected, IF((dates=luDate)*(tblRuns=run)*(tblCans>0),tblCans),
COUNT(selected);
)))
SumMatchesλ
= LAMBDA(luDate, LAMBDA(run, LET(
dates, SCAN("Date", tblDate, LAMBDA(p,d,IF(d>0,d,p))),
selected, IF((dates=luDate)*(tblRuns=run)*(tblCans>0),tblCans),
SUM(selected)
)));
thank you!!!I think the merged cells have done a huge amount of self-inflicted damage when it comes to developing clean calculations! I have been able to solve the problem using 365
If you want an easier option just use helper ranges to improve the structure of your data,Code:"WorksheetFomulae" = MAP(lookupRun1, CountMatchesλ(lookupDate1)); = MAP(lookupRun1, SumMatchesλ(lookupDate1)); "Lambda functions" CountMatchesλ = LAMBDA(luDate, LAMBDA(run, LET( dates, SCAN("Date", tblDate, LAMBDA(p,d,IF(d>0,d,p))), selected, IF((dates=luDate)*(tblRuns=run)*(tblCans>0),tblCans), COUNT(selected); ))) SumMatchesλ = LAMBDA(luDate, LAMBDA(run, LET( dates, SCAN("Date", tblDate, LAMBDA(p,d,IF(d>0,d,p))), selected, IF((dates=luDate)*(tblRuns=run)*(tblCans>0),tblCans), SUM(selected) )));
what does that luDate means?luDate