Results 1 to 6 of 6

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

  1. #1

    How to use a time frame within a sumproduct formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. #2
    Conjurer CheshireCat's Avatar
    Join Date
    Dec 2011
    Location
    Victoria, Canada
    Posts
    118
    Articles
    0
    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 by CheshireCat; 2012-08-01 at 07:59 PM.

  3. #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. #4

    sample attached

    TEST SCHEDULE.xls


    Quote Originally Posted by Bob Phillips View Post
    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. #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. #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
  •