# SUMPRODUCT for Multiple Criteria

1. ## SUMPRODUCT for Multiple Criteria

See attached workbook. Basically, it uses the SUMPRODUCT function to count the number of overlapping dates and times (cell E2 no down). However, I need to modify it to take into account another column - employee ID, as illustrated in Cell F2 on down (Overlap 2 - result only though, looking for revised formula).

So basically, it would count the number of overlapping dates and times for each employee. Any ideas? Thanks.

Time.xlsx  Register To Reply

2. ## Re: SUMPRODUCT for Multiple Criteria

This seems to work?
=SUMPRODUCT((\$B\$2:\$B\$9=B2)*(\$A\$2:\$A\$9+\$C\$2:\$C\$9>=A2+C2)*(\$A\$2:\$A\$9+\$C\$2:\$C\$9<=A2+D2)+(\$A\$2:\$A\$9+\$C\$2:\$C\$9<A2+C2)*(\$A\$2:\$A\$9+\$D\$2:\$D\$9>=A2+C2))  Register To Reply

3. ## Re: SUMPRODUCT for Multiple Criteria

Actually, it is giving me an incorrect count in the last two cells (G8 and G9)..Any idea why?

As this example shows, there is only one employee/date that should have "2" displayed  Register To Reply

4. ## Re: SUMPRODUCT for Multiple Criteria

Sorry, try this...
=SUMPRODUCT(--(\$B\$2:\$B\$9=B2),(\$A\$2:\$A\$9+\$C\$2:\$C\$9>=A2+C2)*(\$A\$2:\$A\$9+\$C\$2:\$C\$9<=A2+D2)+(\$A\$2:\$A\$9+\$C\$2:\$C\$9<A2+C2)*(\$A\$2:\$A\$9+\$D\$2:\$D\$9>=A2+C2))  Register To Reply

5. ## Re: SUMPRODUCT for Multiple Criteria

For some reason, that one did not work either. But, I was able to figure it out based on your first example..I had to add the \$B\$2:\$B\$9=\$B2 to the other side of the formula past the plus sign...Thanks for the quick response..

=(SUMPRODUCT((\$B\$2:\$B\$9=\$B2)*(\$A\$2:\$A\$9+\$C\$2:\$C\$9>=A2+C2)*(\$A\$2:\$A\$9+\$C\$2:\$C\$9<=A2+D2)+(\$B\$2:\$B\$9=\$B2)*(\$A\$2:\$A\$9+\$C\$2:\$C\$9<A2+C2)*(\$A\$2:\$A\$9+\$D\$2:\$D\$9>=A2+C2)))  Register To Reply

6. ## Re: SUMPRODUCT for Multiple Criteria

Curiois, it worked for me...
 A B C D E F 1 Date Employee ID Start Time End Time OverLap Overlap 2 2 01/05/2010 123 12:33 14:15 2 2 3 01/05/2010 345 9:00 10:00 1 1 4 01/05/2010 123 13:15 17:30 2 2 5 02/14/2013 123 8:35 10:15 1 1 6 02/14/2013 345 12:00 14:00 1 1 7 06/01/2014 123 9:00 13:15 1 1 8 06/01/2014 345 11:00 16:15 1 1 9 06/01/2014 456 12:00 13:00 1 1

E2=SUMPRODUCT(--(\$B\$2:\$B\$9=B2),(\$A\$2:\$A\$9+\$C\$2:\$C\$9>=A2+C2)*(\$A\$2:\$A\$9+\$C\$2:\$C\$9<=A2+D2)+(\$A\$2:\$A\$9+\$C\$2:\$C\$9<A2+C2)*(\$A\$2:\$A\$9+\$D\$2:\$D\$9>=A2+C2))
copied across

Note the use of --(\$B\$2:\$B\$9=B2),(.... and not (\$B\$2:\$B\$9=B2)*(....  Register To Reply

7. ## Re: SUMPRODUCT for Multiple Criteria

Hmm...that time it did work. Must have copied it wrong the first time. Thank FDibbins..  Register To Reply

8. ## Re: SUMPRODUCT for Multiple Criteria

Happy to help, thanks for the feedback   Register To Reply