# How can i count as 1 occurence if some cells are merged.

#### Peter Bartholomew

##### Well-Known Member
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
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)
)));``````
If you want an easier option just use helper ranges to improve the structure of your data,

#### Attachments

• 17.3 KB Views: 5
• Henry_paul_616

#### Henry_paul_616

##### New Member
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
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)
)));``````
If you want an easier option just use helper ranges to improve the structure of your data,
thank you!!!

#### Peter Bartholomew

##### Well-Known Member
It is only a dummy variable used within each of the two Lambda functions. According to the parameter passed when it is called, it might be lookupRun1 or lookupRun2, so allowing each function to return different values in the two calculation areas.