Is A "Rolling Count" Possible?

Question

I have a wait staff of 50 , and I am trying to keep a "rolling count", if possible, of how many servers in the last seven days have reached a goal of selling 11 of our specialty items.  For purpose of my question, let's say I only have four servers, I am only looking back 3 days, and my data looks like this (where "Date" is cell A1):

Date       Tom       John       Mary       Sue

7-1         1            11          2             8

7-2         3            9            11           11

7-3         6            1            4             10

7-4         10          5            7             6

7-5         4            10          11           2

Thus, at the end of 7-3 the count would be 3; at 7-4 it would be 2; at 7-5 it would be 1.

I have a formula that seems to work, but it only looks at data in one column at a time.  That formula, using Mary's data from above, would like this:

=COUNTIF(OFFSET(D2,MAX(0,COUNTA(D2:D31)-3), ,3,1),"11")

I think I understand OFFSET, but I am unable to get a formula to look at data from more than one column.  I keep the formula below the data range in column F.

Thank you in advance for any and all help, it is greatly appreciated

WOLLAM

Answer 1

I left the first row blank and started your layout in cell A2.

This formula is only required in one cell.

It must not be placed below the data or it could affect the count.

The area below the data must be blank.

   =COUNTIF(OFFSET(B3,COUNTA(B3:B31)-3,0,3,4),">10")

The "4" specifies the number of columns (servers) you wish to count. 

'---

Jim Cone

Portland, Oregon USA

free & commercial excel stuff

https://goo.gl/IUQUN2 (Dropbox)

Answer 2

I am looking for how many times they have sold 11 in one day.  So, it is per day, not total.  And, I am looking for all servers, not just Mary's last three days.  Does this help?

Hi,

Put this in F4 and drag down. You can change this to accommodate as many columns as you require by simply altering the range.

=COUNTIF(B2:E4,11)

Answer 3

Assuming that the word "Date" starts in A1 from your example above, the following formula entered in cell F4 and copied down should give the right answers:

=SUM(--(B2:B4>=11)*1,--(C2:C4>=11)*1,--(D2:D4>=11)*1,--(E2:E4>=11)*1)

You need to enter this as an array formula (press and hold Ctrl, Shift and Enter to do this)- it will place curly brackets around your formula.

Answer 4

I am looking for how many times they have sold 11 in one day.  So, it is per day, not total.  And, I am looking for all servers, not just Mary's last three days.  Does this help?

Answer 5

Re:  "Thus, at the end of 7-3 the count would be 3; at 7-4 it would be 2; at 7-5 it would be 1."

The logic or the addition does not compute...

On 7-5 they all have met the goal of selling 11 items over the last 3 days: 20, 16, 22, 18.

Explain a little more...

'---

Jim Cone

Portland, Oregon USA