Countif value between two dates

[Gary]

We have a sheet of quotes. In one column we record the
date if a quote is sold. I want to get a count of the
number of sales in a given month. I am trying to use
countif but cannot get the date range in the criteria.
Any suggestions?

 

[Ron de Bruin]

You can use sumproduct, this count the dates in month 1

=SUM((MONTH(B1:B10)=1)*1)

Or with year and month

=SUMPRODUCT( (YEAR(B1:B10)=2003)*(MONTH(B1:B10)=1)*1)

 

[Peo Sjoblom]

Maybe

=SUMPRODUCT((MONTH(B1:B10)=1)*1)

<g>

a heads-up though, that formula will return 10 if the range is empty since
date 0 is
01/00/1900

 

[Peo Sjoblom]

Maybe I should have posted a workaround

=SUMPRODUCT((MONTH(B1:B10)=1)*(ISNUMBER(B1:B10)))

 

[Ron de Bruin]

Oops

Thanks Peo

 

[Ron de Bruin]

I forgot to say this
=SUM((MONTH(B1:B10)=1)*1)

If you Enter it as a array formula it is working

 

[Ron Rosenfeld]

We have a sheet of quotes. In one column we record the
date if a quote is sold. I want to get a count of the
number of sales in a given month. I am trying to use
countif but cannot get the date range in the criteria.
Any suggestions?

I assume when you say month that you mean a particular month of a particular
year, and not all of the years.

Assume a date in the desired month in A1:

=COUNTIF(rng, ">="&DATE(YEAR(A1),MONTH(A1),1) -
COUNTIF(rng, ">"&DATE(YEAR(A1),MONTH(A1)+1,0)


--ron