Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Function, complex criteria
Hi, Hope you all had a good christmas and new year. I'm trying to produce a holiday tracker and need to distinguish between days booked off and days had off. I'm trying to use the countif function to count cells in a row that contain a certain value "B", for booked off and are less than todays date, these would be days had off. The date is held at the top of each column, and I am using =Today() in B2 to get todays date. Is it possible to use the countif function to do this, i.e. something like this: =COUNTIF(Q6:AS6,AND("B",OFFSET(ActiveCell,0,-2)<B2)) I know I could write a function that would achieve this goal, I'm just wondering if there is a quicker way. Cheers, T -- Tomski ------------------------------------------------------------------------ Tomski's Profile: http://www.excelforum.com/member.php...o&userid=26824 View this thread: http://www.excelforum.com/showthread...hreadid=499358 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Function, complex criteria
=SUMPRODUCT(--(Q6:AS6="B"),--(Range_with_Dates<TODAY()))
note that the ranges need to be of same size -- Regards, Peo Sjoblom "Tomski" wrote in message ... Hi, Hope you all had a good christmas and new year. I'm trying to produce a holiday tracker and need to distinguish between days booked off and days had off. I'm trying to use the countif function to count cells in a row that contain a certain value "B", for booked off and are less than todays date, these would be days had off. The date is held at the top of each column, and I am using =Today() in B2 to get todays date. Is it possible to use the countif function to do this, i.e. something like this: =COUNTIF(Q6:AS6,AND("B",OFFSET(ActiveCell,0,-2)<B2)) I know I could write a function that would achieve this goal, I'm just wondering if there is a quicker way. Cheers, T -- Tomski ------------------------------------------------------------------------ Tomski's Profile: http://www.excelforum.com/member.php...o&userid=26824 View this thread: http://www.excelforum.com/showthread...hreadid=499358 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Function, complex criteria
You need to use the SUMPRODUCT function.
=SUMPRODUCT(--(Q6:AS6="B"),--(Q4:AS4<B2)) You might have to adjust it a little bit, because I couldn't understand what you were trying to reference with the OFFSET function. The -- turns the array of logical values to an array of 1's and 0's. Ie. {TRUE,TRUE,FALSE,...}-{1,1,0} so they can be multiplied together. "Tomski" wrote: Hi, Hope you all had a good christmas and new year. I'm trying to produce a holiday tracker and need to distinguish between days booked off and days had off. I'm trying to use the countif function to count cells in a row that contain a certain value "B", for booked off and are less than todays date, these would be days had off. The date is held at the top of each column, and I am using =Today() in B2 to get todays date. Is it possible to use the countif function to do this, i.e. something like this: =COUNTIF(Q6:AS6,AND("B",OFFSET(ActiveCell,0,-2)<B2)) I know I could write a function that would achieve this goal, I'm just wondering if there is a quicker way. Cheers, T -- Tomski ------------------------------------------------------------------------ Tomski's Profile: http://www.excelforum.com/member.php...o&userid=26824 View this thread: http://www.excelforum.com/showthread...hreadid=499358 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Function, complex criteria
Maybe exclude blank dates as well
=SUMPRODUCT(--(Q6:AS6="B"),--(Range_with_Dates<TODAY()),--(Range_with_Dates< "")) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Peo Sjoblom" wrote in message ... =SUMPRODUCT(--(Q6:AS6="B"),--(Range_with_Dates<TODAY())) note that the ranges need to be of same size -- Regards, Peo Sjoblom "Tomski" wrote in message ... Hi, Hope you all had a good christmas and new year. I'm trying to produce a holiday tracker and need to distinguish between days booked off and days had off. I'm trying to use the countif function to count cells in a row that contain a certain value "B", for booked off and are less than todays date, these would be days had off. The date is held at the top of each column, and I am using =Today() in B2 to get todays date. Is it possible to use the countif function to do this, i.e. something like this: =COUNTIF(Q6:AS6,AND("B",OFFSET(ActiveCell,0,-2)<B2)) I know I could write a function that would achieve this goal, I'm just wondering if there is a quicker way. Cheers, T -- Tomski ------------------------------------------------------------------------ Tomski's Profile: http://www.excelforum.com/member.php...o&userid=26824 View this thread: http://www.excelforum.com/showthread...hreadid=499358 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Function, complex criteria
Cheers guys, thats the one. Thanks again. T -- Tomski ------------------------------------------------------------------------ Tomski's Profile: http://www.excelforum.com/member.php...o&userid=26824 View this thread: http://www.excelforum.com/showthread...hreadid=499358 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using ADD function within DCOUNT criteria | Excel Discussion (Misc queries) | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
Countif Function -Nested | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions | |||
Can I use a cell reference in the criteria for the sumif function. | Excel Worksheet Functions |