Saturday, March 9, 2013

yogi_Formulate Running Count of Assignments (listed in another sheet) Over A Specified Number of Days


                                          Google Spreadsheet   Post  #1075
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 9, 2013
user bcrowder7 :(http://productforums.google.com/forum/?zx=nbznyz1cryoz#!mydiscussions/docs/D5iiq9vC6b0)
Keeping a running average of most recent criteria
Hello,

I would really appreciate help with this.  I work on gDocs all the time, so I usually search for a while and try my best it in order to figure it out, but this time I'm incredibly stumped.

Right now I am building a spreadsheet that records the work assignments of each employee on my shift each day.  
Column B has the date, with each work day beginning on each new row.  
Columns C-T are headed by each employee's last name.
Each cell has a dropdown list of one of several types of assignments (Ex: Main Operations, Cleanings, Steamings, Buffers, Columns, Training) 
In Sheet 2, I started making data tables that count the number of times a certain work assignment was assigned to a particular individual.  However, I cannot just have a table that shows the total count.
In terms of using this data, I want to be able to keep several tables that keep a running count of what has been assigned over the last 14,28,and 56 shifts (1 month, 2 months, and 3 months of work)

So far, I have this: =countif(offset(index((C4:C1000),COUNTA(C4:C1000),(C4:C1000),),-10,,14,1)),"Main Operations")

I have just been testing this on Sheet 1, the sheet with data already entered, but in column D. From what I think I'm doing, the above function will count how many cells contain text, beginning with the first data entry, C4, all the way to C1000, and then the returned cell reference is OFFSET up by 10 and then the index to COUNTIF from would be the last 14 data entries or, basically, the last 14 days worked.

I have been struggling with this goal for hours, without breaks, and I would really appreciate some help, or simplification if possible!

Thank you for your time and help!
---
---------------------------------------------------------------------------------------------------------
following is a solution to a bit more generalized problem