# Thread: How to use a time frame within a sumproduct formula

1. ## How to use a time frame within a sumproduct formula

Hello Everyone,

And thank you for all the help I've been getting from the forum's. I have formula, I'm trying to calculate the number of employees that working within a specific time frame of an hour. It's trying to calculate all the male employees present at 00:00 hours. And eventually hour each hour of the day, also with other attributes. Though the data it's using for hours is a shift time like 04:00-12:00 etc... So I think that's where I'm having the issue.

Code:
`=SUMPRODUCT(TEST14DAY!\$D\$4:\$D\$1500,--(TEST14DAY!\$I\$4:\$I\$1500=NOTES!\$D\$24),--(TEST14DAY!\$J\$4:\$J\$1500='DAY ONE'!C3),--(NOTES!\$D\$2:\$D\$90="M"))`
The first range is the column of gender, the second is a work location, the third is shift time with the single cell reference of the hour, 0:00, and the last range ,Notes, is the reference for attributes "M" that reference is to sum all the M's present in the first range.

I used a formula like similar but without any time info and worked perfectly.

Thanks I appreciate any help that you can offer

MZING81

2. The range NOTES!\$D\$2:\$D\$90 is not the same size as the other ranges. Should it be NOTES!\$D\$4:\$D\$1500 instead?

3. I think that the fact that the gender test is against another worksheet means that you are trying to do something that you are not explaining, maybe you have a name on the TEST14DAY worksheet and you want to lookup and compare against that name's gender. Posting the workbook might help us suss it out.

4. ## sample attached

TEST SCHEDULE.xls

Originally Posted by Bob Phillips
I think that the fact that the gender test is against another worksheet means that you are trying to do something that you are not explaining, maybe you have a name on the TEST14DAY worksheet and you want to lookup and compare against that name's gender. Posting the workbook might help us suss it out.

Here's a sample work book, with totals being accumlated on the day one sheet.

5. That data bears so little relationship to the data that one would have imagined from the formula first shown that I think you should explain what you are trying to sum, and how the various data elements connect.

6. The formula is to be inputted into the day one sheet. For example I'm first starting to sum the number male employees working between 0:00 hours 01:00 hours at a specific location!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•