Hi, we have a very complex sales forecast spreadsheet whereby we need to
calculate the number of orders and quotes (between two dates) from the
information entered by the sales people, they enter the quote or order date
and the formula calculates based upon this date. We find this works for all
other dates in the month except it will not count the 1st! Any ideas guys,
we don't want to be fudging the dates.


Formula used: =COUNTIF('Live
Forecast'!$AK$6:$AK$6882,"<=39202")-COUNTIF('Live
Forecast'!$AK$6:$AK$6886,"<=39173") (This is for APRIL).
Office/Excel Ver. 2003.

You are subtracting the number of days that are less than OR EQUAL TO April 1

Change the second COUNTIF from "<=" to "<"

"Ang" wrote:

> Hi, we have a very complex sales forecast spreadsheet whereby we need to
> calculate the number of orders and quotes (between two dates) from the
> information entered by the sales people, they enter the quote or order date
> and the formula calculates based upon this date. We find this works for all
> other dates in the month except it will not count the 1st! Any ideas guys,
> we don't want to be fudging the dates.
>
>
> Formula used: =COUNTIF('Live
> Forecast'!$AK$6:$AK$6882,"<=39202")-COUNTIF('Live
> Forecast'!$AK$6:$AK$6886,"<=39173") (This is for APRIL).
> Office/Excel Ver. 2003.
>
>
>
>
>

Maybe...
=COUNTIF('Live Forecast'!$AK$6:$AK$6882,"<=39202")
-COUNTIF('Live Forecast'!$AK$6:$AK$6886,"<39173")

(dropped the = sign on the second =countif().)

Personally, I find this easier to read:
=COUNTIF('Live Forecast'!$AK$6:$AK$6882,"<="&date(2007,4,30))
-COUNTIF('Live Forecast'!$AK$6:$AK$6886,"<"&date(2007,4,1))





Ang wrote:
>
> Hi, we have a very complex sales forecast spreadsheet whereby we need to
> calculate the number of orders and quotes (between two dates) from the
> information entered by the sales people, they enter the quote or order date
> and the formula calculates based upon this date. We find this works for all
> other dates in the month except it will not count the 1st! Any ideas guys,
> we don't want to be fudging the dates.
>
> Formula used: =COUNTIF('Live
> Forecast'!$AK$6:$AK$6882,"<=39202")-COUNTIF('Live
> Forecast'!$AK$6:$AK$6886,"<=39173") (This is for APRIL).
> Office/Excel Ver. 2003.

--

Dave Peterson

Thanks guys, i will give this a go.
I knew it would be simple!


"Dave Peterson" wrote:

> Maybe...
> =COUNTIF('Live Forecast'!$AK$6:$AK$6882,"<=39202")
> -COUNTIF('Live Forecast'!$AK$6:$AK$6886,"<39173")
>
> (dropped the = sign on the second =countif().)
>
> Personally, I find this easier to read:
> =COUNTIF('Live Forecast'!$AK$6:$AK$6882,"<="&date(2007,4,30))
> -COUNTIF('Live Forecast'!$AK$6:$AK$6886,"<"&date(2007,4,1))
>
>
>
>
>
> Ang wrote:
> >
> > Hi, we have a very complex sales forecast spreadsheet whereby we need to
> > calculate the number of orders and quotes (between two dates) from the
> > information entered by the sales people, they enter the quote or order date
> > and the formula calculates based upon this date. We find this works for all
> > other dates in the month except it will not count the 1st! Any ideas guys,
> > we don't want to be fudging the dates.
> >
> > Formula used: =COUNTIF('Live
> > Forecast'!$AK$6:$AK$6882,"<=39202")-COUNTIF('Live
> > Forecast'!$AK$6:$AK$6886,"<=39173") (This is for APRIL).
> > Office/Excel Ver. 2003.
>
> --
>
> Dave Peterson
>