# Number of Pay Periods in Range with SumProduct

1. ## Number of Pay Periods in Range with SumProduct

Hi Folks,

I am trying to calculate the number of pay periods falling in between two dates, based off a table of pay period dates and coverage start / end dates. Sample spreadsheet attached.

=SUMPRODUCT(--(Payroll!\$D\$2:\$D\$27>='List of Enrolled EE''s'!B2)*(--(Payroll!\$E\$2:\$E\$27<='List of Enrolled EE''s'!C2)))

This is my working formula. It's correct some of the time but not all. I can't just add +1 to it, as then my correct numbers are changed too.

Thoughts? Thanks so much for your help!

2. ## Re: Number of Pay Periods in Range with SumProduct

I find no dates in Payroll!\$E\$2:\$E\$27 <= 1/2/2015. So the zero in D28 of ‘List of Enrolled EE's’ is correct. I haven’t checked the others.

Edit I checked the rest of the cells marked 'should be' and they are correct, too.

3. ## Re: Number of Pay Periods in Range with SumProduct

Thanks. Sorry - I realized my sample spreadsheet's formula was off by a column. It should have been referencing start and end dates for the pay periods but ended up referencing paycheck date instead.

I need to correctly count the # of pay periods that occurred between a person's coverage effective date and coverage end date. If an employee has coverage during any portion of a payroll, they'll have a deduction taken from their paycheck.

For ex. Morticia Addams with coverage from 1/1 - 1/2, there should be one pay period within which those dates fall (the pay period running 12/31/14 - 1/3/15. So it should return 1, not 0.

4. ## Re: Number of Pay Periods in Range with SumProduct

For ex. Morticia Addams with coverage from 1/1 - 1/2, there should be one pay period within which those dates fall (the pay period running 12/31/14 - 1/3/15. So it should return 1, not 0.

Should holidays and weekends be considered? 1/1/15 is a holiday and 1/3/15 is a Saturday.

Also in the above you indicate pay period running 12/31/14 - 1/3/15. I don't know if it will matter or not but there is

a pay period 12/21/14 - 1/3/15 but not 12/31/14 - 1/3/15.

I assume one of these is a typo. Which one? If the period is supposed to be from 12/21 there is another holiday if it matters.

I still cannot get all of the counts to agree with your expected numbers. I keep coming up with new disagreements, though. LOL

5. ## Re: Number of Pay Periods in Range with SumProduct

Try this in D2 filled down.
Formula:
`Please Login or Register  to view this content.`

Edit You almost had it. The references just needed to be crossed.

6. ## Re: Number of Pay Periods in Range with SumProduct

Also this works and does not require arrays.
Formula:
`Please Login or Register  to view this content.`

7. ## Re: Number of Pay Periods in Range with SumProduct

Thank you so much! This did the trick perfectly. I really appreciate it.

8. ## Re: Number of Pay Periods in Range with SumProduct

You are welcome. Thank you for the feedback.

There are currently 1 users browsing this thread. (0 members and 1 guests)