How do I....

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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??
 
Not sure what exactly you require but ....

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

will give number of dates in B which are September

HTH
 
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))
 
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"))))
 
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
 
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.
 
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
 
Back
Top