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

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

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

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

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