Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Sum based on Valueof different cells.

  1. #1
    Board Regular
    Join Date
    Mar 2003
    Posts
    110
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sum based on Valueof different cells.

    One of the managers at my employer is trying to implement a new "attendance tracker" in Excel based on new attendance criteria we are implementing.

    The basic concept of the policy is: Leaving Early or Calling Out garner 1 occurrence, being tardy garners 1/3 of one occurrence (so 3 tardy = 1 occurrence). 2 Occurrences earn the employee a written warning. Each occurrence sets a "trigger date" which begins a "90 day rolling period" of time, after which all occurrences disappear as if they've never happened. However, if you earn another occurrence in this time frame it adds an additional 90 days with the date of the latest occurrence being the new "trigger date." After 90 days occurrence free, your current occurrences are wiped clean.

    Because there is this rolling 90 day period

    Hopefully that makes sense...

    So here is the dilemma. He wants the spreadsheet to do the following:

    If there is no trigger date, then base the count on the Start Date through Today's date. - This is done.
    If there is a trigger date, base the count on the "Start Date" through the "End Date". The "End Date" varies based on cell B29 and whether it is "First," "Second," or "Third."

    After row 33 on the spreadsheet is just a running list of dates in Column A and in Column B we notate "Late" "Early" or "Out". We cannot seem to make it count only through the date ranges mentioned above rather than counting from B33 down.

    Below is the layout of the spreadsheet.

    Thanks!

    Code:
                               A                               B
    6          Attendance Standings                  1st Written Warning
    7                 LATE (arrival)			   1
    8                 LEAVE (early)			           1
    9                 (called) OUT			           1
    10              TTL Occurrences			           2.3
    11	
    25	
    26                  Start Date			           10/24/15
    27                    Today				   1/22/16
    28                Trigger Date	
    29                    Type	
    30                   End Date	
    31	
    32                 Agent Name	
    33                   01/01/16	
    34                   01/02/16			           LATE
    35                   01/03/16			           EARLY
    36                   01/04/16			           OUT
    37                   01/05/16	
    38                   01/06/16

  2. #2
    Board Regular Rijnsent's Avatar
    Join Date
    Oct 2005
    Location
    Utrecht, Holland
    Posts
    861
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum based on Valueof different cells.

    Hi JokerFMJ,

    you probably have a COUNTIF function running on B33 and below? What you could do is use a combination of an OFFSET and MATCH formulas to fill the range in your COUNTIF formula. See for some ideas this site: Using OFFSET MATCH to pull in Employee sales by Month - Excel by Joe
    What I think I would do is just add some helper columns (C, D) to calculate the running Trigger date and the running occurrences. It might look messier, but it gives more controls to check your results.

    Ciao,

    Koen
    You can't post attachments here, but you can help me helping you by posting a screen shot directly in your post with any of those tools.
    And although it's against forum rules, otherwise use dropbox/google drive/etc to get your file accross.
    Finally, please show that you made an effort to solve your problem: Yes, I like to help, but am not going to do your job.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com