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

[Henry_paul_616]

Hi guys, Can some one give me a idea for how to count the RUNS code LINE by date and if the CANS line are merged, count RUNS as 1.
In the end i need to Count the number of runs by date and sum the cans by date according the date and RUNS line codes.

 

[Peter Bartholomew]

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

"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,

 

[Henry_paul_616]

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

"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!!!

 

[Henry_paul_616]

luDate

what does that luDate means?

 

[Peter Bartholomew]

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.