How do I....
 

I need to count the number of cells that have dates in them based on the
month which is listed in column A (months are listed as sept, oct, Nov) How
can I do this??

How do I....
 

Not sure what exactly you require but ....

=SUMPRODUCT(--(month(B2:B1000)=9))

will give number of dates in B which are September

HTH

"TDW" wrote:

I need to count the number of cells that have dates in them based on the
month which is listed in column A (months are listed as sept, oct, Nov) How
can I do this??

How do I....
 

try this -

I created a simple spreadsheet as follows:

col A is the date
1/1/07
1/2/07...


col. B is a helper column to get the month
=month(a1)

Somewhere on the spreadsheet create a lookup table so that you can
convert the month from Jan, Feb... format to 1, 2, ...
I did it in F2:G13
F1 = Jan G1 = 1
F2 = Feb G2 = 2...


Assume you enter your first date in Cell A1, and the month you want to
count is in G23... then the following formula will get your answer.

=countif($b1:$B26, vlookup(G23,$f$2:$g$13,2,false))


On Aug 15, 4:18 pm, TDW wrote:
I need to count the number of cells that have dates in them based on the
month which is listed in column A (months are listed as sept, oct, Nov) How
can I do this??

How do I....
 

Hi,

countif(A1:A100,"sept")
countif(A1:A100,"oct")
countif(A1:A100,"nov")

Thanks,
--
Farhad Hodjat


"TDW" wrote:

I need to count the number of cells that have dates in them based on the
month which is listed in column A (months are listed as sept, oct, Nov) How
can I do this??

How do I....
 

Topper: I think you got it close. We need to convert sept to the number 9.
Putting it in a datevalue function will get this result.


=SUMPRODUCT(--(MONTH(B2:B1000)=MONTH(DATEVALUE("1-"&A1&"-07"))))

"Toppers" wrote:

Not sure what exactly you require but ....

=SUMPRODUCT(--(month(B2:B1000)=9))

will give number of dates in B which are September

HTH

"TDW" wrote:

I need to count the number of cells that have dates in them based on the
month which is listed in column A (months are listed as sept, oct, Nov) How
can I do this??

How do I....
 

You can simplify that by using

=SUMPRODUCT(--(TEXT(B2:B1000,"mmm")=A1))

or to guard against if A1 could be either Sep or September


=SUMPRODUCT(--(TEXT(B2:B1000,REPT("m",MIN(4,LEN(A1))))=A1))


note that blank cells score as January so to test for a numerical date in B
might be a good idea



=SUMPRODUCT(--(TEXT(B2:B1000,REPT("m",MIN(4,LEN(A1))))=A1),--(ISNUMBER(B2:B1000)))





--
Regards,

Peo Sjoblom






"Joel" wrote in message
...
Topper: I think you got it close. We need to convert sept to the number
9.
Putting it in a datevalue function will get this result.


=SUMPRODUCT(--(MONTH(B2:B1000)=MONTH(DATEVALUE("1-"&A1&"-07"))))

"Toppers" wrote:

Not sure what exactly you require but ....

=SUMPRODUCT(--(month(B2:B1000)=9))

will give number of dates in B which are September

HTH

"TDW" wrote:

I need to count the number of cells that have dates in them based on
the
month which is listed in column A (months are listed as sept, oct, Nov)
How
can I do this??

How do I....
 

You code won't work because sept is four letters and your code only produces
3 letters. Sept will not compare. the date value function will convert both
SEP and SEPT to the month number.

"Peo Sjoblom" wrote:

You can simplify that by using

=SUMPRODUCT(--(TEXT(B2:B1000,"mmm")=A1))

or to guard against if A1 could be either Sep or September


=SUMPRODUCT(--(TEXT(B2:B1000,REPT("m",MIN(4,LEN(A1))))=A1))


note that blank cells score as January so to test for a numerical date in B
might be a good idea



=SUMPRODUCT(--(TEXT(B2:B1000,REPT("m",MIN(4,LEN(A1))))=A1),--(ISNUMBER(B2:B1000)))





--
Regards,

Peo Sjoblom






"Joel" wrote in message
...
Topper: I think you got it close. We need to convert sept to the number
9.
Putting it in a datevalue function will get this result.


=SUMPRODUCT(--(MONTH(B2:B1000)=MONTH(DATEVALUE("1-"&A1&"-07"))))

"Toppers" wrote:

Not sure what exactly you require but ....

=SUMPRODUCT(--(month(B2:B1000)=9))

will give number of dates in B which are September

HTH

"TDW" wrote:

I need to count the number of cells that have dates in them based on
the
month which is listed in column A (months are listed as sept, oct, Nov)
How
can I do this??

How do I....
 

Why would anyone use Sept, does anyone use Octo or Nove?


Of course that can be easily fixed

=SUMPRODUCT(--(TEXT(B2:B1000,"mmm")=LEFT(A1,3)))


=SUMPRODUCT(--(TEXT(B2:B1000,"mmm")=LEFT(A1,3)),--(ISNUMBER(B2:B1000)))




Regardless your formula will return an erroneous result if there are blanks
in B2:B1000
and DATEVALUE is a totally obsolete function


=SUMPRODUCT(--(MONTH(B2:B1000)=MONTH(--("1-"&A1&"-07"))))

or to fix the blank cell errors


=SUMPRODUCT(--(MONTH(B2:B1000)=MONTH(--("1-"&A1&"-07")--(ISNUMBER(B2:B1000)))))


HTH


--
Regards,

Peo Sjoblom




"Joel" wrote in message
...
You code won't work because sept is four letters and your code only
produces
3 letters. Sept will not compare. the date value function will convert
both
SEP and SEPT to the month number.

"Peo Sjoblom" wrote:

You can simplify that by using

=SUMPRODUCT(--(TEXT(B2:B1000,"mmm")=A1))

or to guard against if A1 could be either Sep or September


=SUMPRODUCT(--(TEXT(B2:B1000,REPT("m",MIN(4,LEN(A1))))=A1))


note that blank cells score as January so to test for a numerical date in
B
might be a good idea



=SUMPRODUCT(--(TEXT(B2:B1000,REPT("m",MIN(4,LEN(A1))))=A1),--(ISNUMBER(B2:B1000)))





--
Regards,

Peo Sjoblom






"Joel" wrote in message
...
Topper: I think you got it close. We need to convert sept to the
number
9.
Putting it in a datevalue function will get this result.


=SUMPRODUCT(--(MONTH(B2:B1000)=MONTH(DATEVALUE("1-"&A1&"-07"))))

"Toppers" wrote:

Not sure what exactly you require but ....

=SUMPRODUCT(--(month(B2:B1000)=9))

will give number of dates in B which are September

HTH

"TDW" wrote:

I need to count the number of cells that have dates in them based on
the
month which is listed in column A (months are listed as sept, oct,
Nov)
How
can I do this??