Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.nl.office.excel
|
|||
|
|||
Big challenge!! Formula for count last 10 columns since today!
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 |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.nl.office.excel,microsoft.public.excel
|
|||
|
|||
Big challenge!! Formula for count last 10 columns since today!
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 |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.nl.office.excel,microsoft.public.excel
|
|||
|
|||
Big challenge!! Formula for count last 10 columns since today!
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 |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.nl.office.excel,microsoft.public.excel
|
|||
|
|||
Big challenge!! Formula for count last 10 columns since today!
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count days in between or if not entered till TODAY | Excel Discussion (Misc queries) | |||
Count Dates <Today? | Excel Discussion (Misc queries) | |||
count if gone past today | Excel Discussion (Misc queries) | |||
Formula to count number of days in range which are less than today | Excel Worksheet Functions |