need to count days at jobs per employee

[EldenDAre]

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.

 

[awoohaw]

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

 

[EldenDAre]

@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?

 

[EldenDAre]

Figured out DropBox.

Copy of Heavy Job Hours.xlsx

Shared with Dropbox

www.dropbox.com

 

[awoohaw]

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.

 

[awoohaw]

Figured out DropBox.

Copy of Heavy Job Hours.xlsx

Shared with Dropbox

www.dropbox.com

this file has data linked to it in another sharepoint server.

 

[EldenDAre]

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.

 

[Fluff]

How about

=ROWS(UNIQUE(FILTER($E$2:$E$2000,($A$2:$A$2000=A2)*($D$2:$D$2000=D2))))

 

[EldenDAre]

How about

=ROWS(UNIQUE(FILTER($E$2:$E$2000,($A$2:$A$2000=A2)*($D$2:$D$2000=D2))))

It works! My coworker tested hers as well. Saved my life!

 

[Fluff]

Glad we could help & thanks for the feedback.