software

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 quot;Bquot;, 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(quot;Bquot;,OFFSET(ActiveCell,0,-2)lt;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: www.excelforum.com/member.php...oamp;userid=26824
View this thread: www.excelforum.com/showthread...hreadid=499358=SUMPRODUCT(--(Q6:AS6=quot;Bquot;),--(Range_with_Dateslt;TODAY()))

note that the ranges need to be of same size

--

Regards,

Peo Sjoblom

quot;Tomskiquot; gt; wrote in
message ...
gt;
gt; Hi,
gt;
gt; Hope you all had a good christmas and new year.
gt;
gt; I'm trying to produce a holiday tracker and need to distinguish between
gt; days booked off and days had off. I'm trying to use the countif
gt; function to count cells in a row that contain a certain value quot;Bquot;, for
gt; booked off and are less than todays date, these would be days had off.
gt; The date is held at the top of each column, and I am using =Today() in
gt; B2 to get todays date.
gt;
gt; Is it possible to use the countif function to do this, i.e. something
gt; like this:
gt;
gt; =COUNTIF(Q6:AS6,AND(quot;Bquot;,OFFSET(ActiveCell,0,-2)lt;B2))
gt;
gt; I know I could write a function that would achieve this goal, I'm just
gt; wondering if there is a quicker way.
gt;
gt; Cheers,
gt;
gt; T
gt;
gt;
gt; --
gt; Tomski
gt; ------------------------------------------------------------------------
gt; Tomski's Profile:
www.excelforum.com/member.php...oamp;userid=26824
gt; View this thread: www.excelforum.com/showthread...hreadid=499358
gt;
You need to use the SUMPRODUCT function.

=SUMPRODUCT(--(Q6:AS6=quot;Bquot;),--(Q4:AS4lt;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,...}-gt;{1,1,0}
so they can be multiplied together.

quot;Tomskiquot; wrote:

gt;
gt; Hi,
gt;
gt; Hope you all had a good christmas and new year.
gt;
gt; I'm trying to produce a holiday tracker and need to distinguish between
gt; days booked off and days had off. I'm trying to use the countif
gt; function to count cells in a row that contain a certain value quot;Bquot;, for
gt; booked off and are less than todays date, these would be days had off.
gt; The date is held at the top of each column, and I am using =Today() in
gt; B2 to get todays date.
gt;
gt; Is it possible to use the countif function to do this, i.e. something
gt; like this:
gt;
gt; =COUNTIF(Q6:AS6,AND(quot;Bquot;,OFFSET(ActiveCell,0,-2)lt;B2))
gt;
gt; I know I could write a function that would achieve this goal, I'm just
gt; wondering if there is a quicker way.
gt;
gt; Cheers,
gt;
gt; T
gt;
gt;
gt; --
gt; Tomski
gt; ------------------------------------------------------------------------
gt; Tomski's Profile: www.excelforum.com/member.php...oamp;userid=26824
gt; View this thread: www.excelforum.com/showthread...hreadid=499358
gt;
gt;

Maybe exclude blank dates as well

=SUMPRODUCT(--(Q6:AS6=quot;Bquot;),--(Range_with_Dateslt;TODAY()),--(Range_with_Dateslt;
gt;quot;quot;))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Peo Sjoblomquot; gt; wrote in message
...
gt; =SUMPRODUCT(--(Q6:AS6=quot;Bquot;),--(Range_with_Dateslt;TODAY()))
gt;
gt; note that the ranges need to be of same size
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; quot;Tomskiquot; gt; wrote in
gt; message ...
gt; gt;
gt; gt; Hi,
gt; gt;
gt; gt; Hope you all had a good christmas and new year.
gt; gt;
gt; gt; I'm trying to produce a holiday tracker and need to distinguish between
gt; gt; days booked off and days had off. I'm trying to use the countif
gt; gt; function to count cells in a row that contain a certain value quot;Bquot;, for
gt; gt; booked off and are less than todays date, these would be days had off.
gt; gt; The date is held at the top of each column, and I am using =Today() in
gt; gt; B2 to get todays date.
gt; gt;
gt; gt; Is it possible to use the countif function to do this, i.e. something
gt; gt; like this:
gt; gt;
gt; gt; =COUNTIF(Q6:AS6,AND(quot;Bquot;,OFFSET(ActiveCell,0,-2)lt;B2))
gt; gt;
gt; gt; I know I could write a function that would achieve this goal, I'm just
gt; gt; wondering if there is a quicker way.
gt; gt;
gt; gt; Cheers,
gt; gt;
gt; gt; T
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Tomski
gt; gt; ------------------------------------------------------------------------
gt; gt; Tomski's Profile:
gt; www.excelforum.com/member.php...oamp;userid=26824
gt; gt; View this thread:
www.excelforum.com/showthread...hreadid=499358
gt; gt;
gt;
gt;

Cheers guys, thats the one. Thanks again.

T--
Tomski
------------------------------------------------------------------------
Tomski's Profile: www.excelforum.com/member.php...oamp;userid=26824
View this thread: www.excelforum.com/showthread...hreadid=499358