Sum based on Valueof different cells.

[JokerFMJ]

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!

                           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

 

[Rijnsent]

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