• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Henry_paul_616

New Member
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.


80425
 

Attachments

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

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.
 
Top