Formula not working with duplicate information

[Dena]

Can anyone help. I have a spreadsheet that has worked up until today. On tab Student Worker Pay Column F, it is supposed to look at certain variables and compare them tab Student Worker List. It was working fine until I had to have a duplicate entry on Student Worker List but with different dates. If the name, dept, and title match and the dates fall with within the dates listed on the Pay spreadsheet for that employee, it should bring back "A" otherwise "I". Samantha was hired during one period of dates and was rehired for new dates. Since the Name, Dept, and Title are the same for both, the formula isn't bring back the correct data.

Any suggestions????

12-13 student Test.xlsx

[bentleybob]

You're summing dates. I suggest you divide by the equivalent COUNTIFS function.

[Dena]

Im decent in excel but not great, could you please provide an example? I have never used the Countif function.

[bentleybob]

In cell F2, and copied down:

Please Login or Register  to view this content.

[Dena]

This didn't seem to work.


Zayas, Samantha Biology Stu 2 7/2/12 7/31/12 I $8.75 34 17 $297.50 7/31/12 7/31/12
This particular line shows the "I" still but it should be an "A" because she worked this position in July and then had a break and came back in September. Any suggestions?

Because she is listed again on the first tab with the same position even though the dates are different, it's listing any I. I want it to show an A if it falls between either sets of dates.

[bentleybob]

Okay, so neither SUMIFS nor SUMIFS / COUNTIFS works because the dates are different. I think you need to revise the approach to look up in a table that has start/end dates. It will be a bit funky because you need to check for multiple instances, but not too difficult since the ranges shouldn't overlap. I can work on this if you'd like, but unfortunately don't have time right now.

[Dena]

I would really appreciate you taking a look at this when you can. Thanks

[Dena]

Bentleybob, just wanted to check in to see if you have some free time to look at my issue?

[bentleybob]

Does this work? Put in the Pay tab, cell F2, and copy down:

Please Login or Register  to view this content.

[Dena]

Hello Bentleybob, I tried this and it isn't working. It I placed the formula in cell F2 and it changed the existing A to an I which is incorrect.

Any suggestions?

[Dena]

Hello Everyone, I still have not been able to resolve my issue. I am hoping that someone can take a look and come up with a working solution.

Basically on "student worker list" I input the names of students that hired for part time work, by which dept, their title and for which days they are allowed to work. On "student worker pay" I input their actual time worked and for which dates. "Student worker pay" column A,B,C,D,and E are supposed to compare to "student worker list" and match A,B,and C and then if the dates I entered on the "pay" D and E fall within the dates listed on "list" sheet, then column F on "pay" shows as an "A", not an I. I currently have one student ZAYAS that was hired by the same dept and same title different dates. When I input her pay info, it marks her as an "I" because once the formula can't get past the DEPT duplicate. I even tried entering her as a different title and it still comes up as an "I" because it's for the same dept. Does anyone have any suggestions??????

Test.xlsx

Can you also look at sheet "pay" column C's formula because after I input the dates (which are valid), if the Dept is a duplicate but the titles are different, it's not pulling in the data correctly.

[Dena]

Any suggestions??????????