Countif value between two dates

  • Thread starter Thread starter Gary
  • Start date Start date
G

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?
 
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)
 
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
 
Maybe I should have posted a workaround

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

If you Enter it as a array formula it is working
 
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?
Click to expand...

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
 
Back
Top