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

Need a formula to Count Employees and Sum Until reaches a certain dollar amount and return date

Liferg

New Member
Hello,
I am in need of 2 separate formulas. They can be added in columns J and K. I need one formula that can give me the employee count for each employer. And then I need another formula that can sum up the gross pay until it reaches $1,000 and return the date from column H. So for instance the employee count for John Doe is 1, and John Smith 2. And then the date for the gross pay for John Doe would be 1/27/2023, because that's the date where his gross pay hit $1,000. And for John Smith it would be 1/13/2023. I have attached a test spreadsheet. I haven't started any formulas for this. Thank you in advance for your help.
 

Attachments

  • TEST-3.21.23.xlsx
    10.9 KB · Views: 7
I hope you are not expecting this to be simple! Using Lambda functions
Code:
= LET(
    distinctEmployers, UNIQUE(Table1[Employer Name]),
    employeeCounts,    MAP(distinctEmployers, EmployeeCountλ),
    keyDate,           MAP(distinctEmployers, CriticalDateλ),
    HSTACK(distinctEmployers, employeeCounts, keyDate)
  )
where
Code:
EmployeeCountλ(employer)
= LET(
    employees, FILTER(Table1[Employee Name], Table1[Employer Name] = employer),
    COUNTA(UNIQUE(employees))
  )
 
CriticalDateλ(employer)
= LET(
    pay,  FILTER(Table1[Gross Pay],  Table1[Employer Name] = employer),
    date, FILTER(Table1[Check Date], Table1[Employer Name] = employer),
    accumulated, SCAN(0, pay, Sumλ),
    XLOOKUP(1000, accumulated, date, , 1)
  )
 
Sumλ(x, y)
= x + y

83591
Warning: Controversial statement ahead
If you do not use 365, get it. It will finish up cheaper than wasting your time with obsolete code.
 

Attachments

  • TEST-3.21.23.xlsx
    16 KB · Views: 8
I'm using Excel 2021, and here is my formula solution (not remarkably well as Peter's).

1] In L2, enter formula:

=UNIQUE(Table1[Employer Name])

2] In M2, enter formula & copied down:

=COUNTA(UNIQUE(FILTER(Table1[Employee Name],Table1[Employer Name]=L2)))

3] In N2, enter formula & copied down:

=FILTER(Table1[Check Date],Table1[Gross Pay]=LARGE(FILTER(Table1[Gross Pay],Table1[Employer Name]=L2),M2))

83593
 

Attachments

  • TEST-3.21.23 (BY).xlsx
    17.6 KB · Views: 4
I'm using Excel 2021, and here is my formula solution (not remarkably well as Peter's).

1] In L2, enter formula:

=UNIQUE(Table1[Employer Name])

2] In M2, enter formula & copied down:

=COUNTA(UNIQUE(FILTER(Table1[Employee Name],Table1[Employer Name]=L2)))

3] In N2, enter formula & copied down:

=FILTER(Table1[Check Date],Table1[Gross Pay]=LARGE(FILTER(Table1[Gross Pay],Table1[Employer Name]=L2),M2))

View attachment 83593
Thank you so very much Bosco! I appreciate your time and effort on this as well :)
 
Back
Top