Need assistance with "not counting" total occurences??

[eparr1026]

I have a situation where I have employees who receive credit for completing multiple jobs per day. This causes there to be multiple lines in my payroll file for a given employee with the same work date. I am attempting to count the number of days that an employee works in a week. Unfortunately my formula is summing every occurrence where an employee name matches a date within a given range. I need the formula to just count the number of days the employee works that week and not the total number of jobs he gets credit for completing. Hope that makes sense. I have attached a sample file. The formula I am in need of assistance with is in cell Q22. This employee completed 158 jobs for the week but he worked 6 out of 7 days. The six is what I am trying to arrive at is cell Q22. Your assistance would be greatly appreciated.

This is the formula I am currently trying to make work:

=SUM(IF($H$3:$H$15001=U22,IF($F$3:$F$15001=$T$3,1,0)))+SUM(IF($H$3:$H$15001=U22,IF($F$3:$F$15001=$T$4,1,0)))+SUM(IF($H$3:$H$15001=U22,IF($F$3:$F$15001=$T$5,1,0)))+SUM(IF($H$3:$H$15001=U22,IF($F$3:$F$15001=$T$6,1,0)))+SUM(IF($H$3:$H$15001=U22,IF($F$3:$F$15001=$T$7,1,0)))+SUM(IF($H$3:$H$15001=U22,IF($F$3:$F$15001=$T$8,1,0)))+SUM(IF($H$3:$H$15001=U22,IF($F$3:$F$15001=$T$9,1,0)))

Thanks for your help!!!

[Tony Valko]

I didn't look at your file (too big for me!)...

You can replace that long formula with this shorter one...

=SUMPRODUCT(COUNTIFS($H$3:$H$15001,U22,$F$3:$F$15001,$T$3:$T$9))

Not saying that'll solve your problem, though!

[TMS]

Add a Helper column, say column Q and use the formula: =COUNTIFS($F$3:$F3,$F3,$I$3:$I3,$I3) in cell Q3 and copy down.

Then use the Helper column in your formula to select/count only the entries with a value of 1 in column Q.

Regards, TMS

[FDibbins]

or just this....
=COUNTIFS($F:$F,">="&$T$3,$F:$F,"<="&$T$9,$H:$H,$U19)