need to count days at jobs per employee

EldenDAre

New Member
Joined
May 5, 2023
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I have a workbook that takes data from one program, tracks the hours of all the employees, and produces an upload file for another program (accounting software) that processes payroll. As these employees work multiple jobs a weeks, sometimes even multiple in one day, I need to count the days (unique dates) spent at each job per employee. I've been using their PR Code, Job, and Date as my criteria. I have tried IF/INDEX/MATCH, googled some COUNTIFS, FREQUENCY, UNIQUE, SUMPRODUCT and the best I can get is a return of 1. I don't know if I'm overthinking it? Please help me.
1683292311755.png
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
@EldenDAre ,
Would you please help the forum help you? Posting images, only give a picture of the scenario, which a little helpful. But the biggest help is by helping the forum save time and reduce errors in trying to solve your problem. The forum has an add in tool called xl2bb (link below) it allows you to post mini workbooks of the cell ranges you are concerned about. Please use that. If you cannot use that then please post your data as a table being sure to include a description of the formulas (and cell reference that the formula is in) and the row numbers and column numbers.

In both cases, giving expected result is extremely helpful in making sure the forum drills down to your solution most effectively.

But, to discuss your question are you wanting a summary of each PAYROLL CODE (Col A) with number of days? In the image would RANIN have 6?

Thanks in advance.
 
Upvote 0
@EldenDAre ,
Would you please help the forum help you? Posting images, only give a picture of the scenario, which a little helpful. But the biggest help is by helping the forum save time and reduce errors in trying to solve your problem. The forum has an add in tool called xl2bb (link below) it allows you to post mini workbooks of the cell ranges you are concerned about. Please use that. If you cannot use that then please post your data as a table being sure to include a description of the formulas (and cell reference that the formula is in) and the row numbers and column numbers.

In both cases, giving expected result is extremely helpful in making sure the forum drills down to your solution most effectively.

But, to discuss your question are you wanting a summary of each PAYROLL CODE (Col A) with number of days? In the image would RANIN have 6?

Thanks in advance.
Yes Ranin would have 6, that was my test. Anyway, I am unable to use the add-in. Likely due to the restrictions at my work computer. Any other way I can share a copy of the sheet?
1683294417937.png
 
Upvote 0
Yes Ranin would have 6, that was my test. Anyway, I am unable to use the add-in. Likely due to the restrictions at my work computer. Any other way I can share a copy of the sheet?
View attachment 91041
label column headers and row numbers, highlight cells, click copy, come to this thread, click in message, paste.
put formulas and the cells they are in in the description.
 
Upvote 0
label column headers and row numbers, highlight cells, click copy, come to this thread, click in message, paste.
put formulas and the cells they are in in the description.
this file has data linked to it in another sharepoint server.
Yes, for the craft. Can that be deleted? I don't need it for these purposes. I just want to count the days based on the Payroll Code, Job, and unique dates because my data will have multiples of all, I need to count unique dates.
 
Upvote 0
How about
Excel Formula:
=ROWS(UNIQUE(FILTER($E$2:$E$2000,($A$2:$A$2000=A2)*($D$2:$D$2000=D2))))
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top