# How to use a time frame within a sumproduct formula

#### MZING81

##### New member
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

#### CheshireCat

##### New member
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?

Last edited:

#### Bob Phillips

##### Super Moderator
Staff member
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.

#### MZING81

##### New member
sample attached

View attachment TEST SCHEDULE.xls

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.

#### Bob Phillips

##### Super Moderator
Staff member
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.

#### MZING81

##### New member
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!