Big challenge!! Formula for count last 10 columns since today!

[Pieter]

Hi,

I want to count the numbers of cells with a certain value in it, in a range
which starts and ends based on the current date.

I have this list of attendence: For every person we register if they were at
training or game: training are on wednesday and friday, games sometimes on
saturday. For each 'appointment' (training/games-) we have a column. For
each player we have a row. If they were at training, we put "1", if they
weren't but they warned us we put a "v", and if we didn't get any news at
all we put nothing in it.

5-08 7-08 12-08 14-08 19-08 21-08
PEOPLE AUGUST
person 1 1 v v 1 v 1
person 2 v v v 1 1
person 3 1 v 1 1 1 1
person 4 1 v 1 v v


Now I want to know for each person how many times they were there, howm any
times they warned, and how many times they weren't there without warning;
and this for the last 10 appointments. So I have to select a range based on
the current date, and based on the values in the first row, and than perform
a countif on it.

Anybody has any idea how to get to this range? I don't find a formula (or
combination of some of them) which helps me with this :-/ Preferably, this
also should work in Google Docs

Thanks a lot in advance,


Pieter

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Barb Reinhardt]

You can try this if you have dates in the future in row 1

=OFFSET(Sheet1!$A$1,0,SUMPRODUCT(--(Sheet1!$1:$1<=TODAY()),--(ISBLANK(Sheet1!$1:$1)=FALSE)),1,-MIN(SUMPRODUCT(--(Sheet1!$1:$1<=TODAY())),10))

 

[Barb Reinhardt]

This is how I'd do it.

1) Define a named range (in my case it's Last10) and use this definition.
Right now, I'm assuming that you're only entering a column with data if
you've had a practice. If not, the named range will need to be changed.

=OFFSET(Sheet1!$A$1,0,COUNT(Sheet1!$1:$1),1,-MIN(COUNT(Sheet1!$1:$1),10))

Enter CTRL G and type the named range to highlight it and make sure it's
correct.

I'm assuming you have dates in B11 for my test and "Dates" in A1.

R1: 'Last 10 WIthout Warning'
R2: 'Last 10 with warning'
A2: Player 1
R2: =COUNTIF(OFFSET(Last10,ROW()-ROW(Last10),0),"1")
S2: =COUNTIF(OFFSET(Last10,ROW()-ROW(Last10),0),"v")

HTH,

Barb Reinhardt

 

[Pieter]

Hi,

Thanks a lot! I was able to get something that works, whereby DatumTot is a
named cell which contains the current date (TODAY) or a specific date in
case I want to know up to a certain date.

=ARRAYFORMULA(SUMPRODUCT(('Aanwezigheden'!$J$1:$DD$1<=DatumTot);
('Aanwezigheden'!$J$1:$DD$1>=(DatumTot - 28))
;('Aanwezigheden'!$J3:$DD3=1)))

have a nice day,

Pieter