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

Countifs/Sumifs Question

L

Lacoste042

New Member
I believe this is a simple question, but I can not come up with an answer, so I pose the question to the community. I have included a sample file.

I need to countifs unique days worked in a month - will be clear with example in sheet.

Along the same lines, I also need to sumifs if it falls between a given date range and belongs to en employee. I think the problem is that it is non congruent data. I took a shot at this one and I don't think I'm far off, but with excel close doesn't count.

Thanks to anyone who even wants to take a guess or has a suggestion

Joe
 

Attachments

Hi Joe ,

You say that the number of working days in January is 22 , but the number of dates present in column D is 31 for Michele and Mary Ann , while for Roberta it is 30. What is to be done ?

Narayan
 
Sorry, I just pulled a sample from my actual spreadsheet, then changed some actual information to a randbetween function. However the question still remains, how do I see that the number f unique days worked for Mary Ann is actually only 6? Which would be 1/3,7,10,11,12,30
 
Hi ,

It is still not clear how you have concluded that these were the only days Mary Ann worked. Can you clarify ?

Narayan
 
The long way, I filtered by Mary Ann, then removed duplicate by date. But there has to be a dynamic way to see how many day in a month an employee worked
 
Hi ,

We must be looking at different files !

When I filter your uploaded data for Mary Ann , I see all 31 dates of January in column D.

Narayan
 
No we are looking at the same data, the next step would be to remove duplicates based on date. I think we may be getting lost in the sample file. All I am looking to do is get a count of the unique dates between 2 dates with one if (the employee's name)

Have you looked at the second tab, I also need to get s sumif?

I very much appreciate you taking the time to look at my questions.
 
Hi ,

You have given a Start Date of January 1 and an end date of January 31 ; between these two dates , all the 31 dates of January are present for Mary Ann.

I do not know how the question of getting unique dates comes into the picture ; I am able to see all the 31 dates of January.

Unless this issue is sorted out , going on to SUMIFS is not correct , since similar issues can surface there.

Narayan
 
Ok, I see your point, see updated sample file. It was my mistake in not posting an accurate question/sample file.

My goal is to count the number of networkdays that a particular employee works in the given month. I know that January has 22 networkdays, I ultimately need to see how many the employee actually worked. This will be divided by a total (not on sample file) to give me accurate average.
 

Attachments

Hi ,

This is more like it.

One clarification ; you mention working days ; can you clarify whether this is necessary or can we just take unique dates ?

In your present file , Mary Ann has 14 unique dates ; can we assume they are all working days or do we need to check ?

Narayan
 
NICE!!! I plugged in my actual info (named ranges and such) into the formula and it works perfectly.

VERY much appreciated!
 
You must log in or register to reply here.
Back
Top