=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