Countif Function, complex criteria

[Tomski]

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

 

[Peo Sjoblom]

=SUMPRODUCT(--(Q6:AS6="B"),--(Range_with_Dates<TODAY()))

note that the ranges need to be of same size

 

[Sloth]

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.

 

[Bob Phillips]

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)

=SUMPRODUCT(--(Q6:AS6="B"),--(Range_with_Dates<TODAY()))

note that the ranges need to be of same size

 

[Tomski]

Cheers guys, thats the one. Thanks again.