How do I....

T

TDW

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??
 
T

Toppers

Not sure what exactly you require but ....

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

will give number of dates in B which are September

HTH
 
T

Tim879

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))
 
J

Joel

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"))))
 
P

Peo Sjoblom

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
 
J

Joel

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.
 
P

Peo Sjoblom

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
 
Top