How to use a time frame within a sumproduct formula

MZING81

New member
Joined
Mar 27, 2012
Messages
40
Reaction score
0
Points
0
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
Joined
Dec 30, 2011
Messages
121
Reaction score
0
Points
0
Location
Victoria, Canada
Excel Version(s)
Microsoft Excel 2013
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
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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
Joined
Mar 27, 2012
Messages
40
Reaction score
0
Points
0
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
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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
Joined
Mar 27, 2012
Messages
40
Reaction score
0
Points
0
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!
 
Top