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

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.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Pieter" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>

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))

"Pieter" wrote:

> 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
>
>
>

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 B1:P1 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" wrote:

> 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
>
>
>

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

"Barb Reinhardt" <(E-Mail Removed)> wrote in message
news:1031594E-50B0-4DAC-B751-(E-Mail Removed)...
> 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 B1:P1 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" wrote:
>
>> 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
>>
>>
>>