How to Count Months in a Column of Dates

[Dexsquab]

Hello all,

Hope you can assist with a slight problem I've encountered.

I have a large column of date entries (several columns, actually, bu
same applies to all of them).

This column is a Contact Date (and has been named so it can be accesse
by reference as 'contact'), recording when we first interacted with a
individual. It contains values up to ten or so years ago.

There is a report associated with this data, where I need to brea
down, by month, when people contacted us. This information is onl
relevant however to people contacting us in the current financia
year.

At the moment, I have to take the date of contact, pass it through a
AND to see if it falls into the correct year. This obviously leads t
a column of TRUE/FALSEs 1-to-1 related to the contact dates. If it i
TRUE, then I take the Month() value from the contact date. Using
Countif across the column of determined months is used in the repor
(for example,
=countif(contactResults,3)
returns the value for people first seen in March).

I was thinking a VBA script would be able to compress this to a singl
function, except I do not know how to address a column for inpu
purposes. I'm aiming for something akin to the following:


Function newContacts(inputSet As [?], selectedMonth As Integer)
newContacts=0
Do
if ((inputSet[element]>=(1st date of fin.year)) AND
(inputSet[element]<=(last date of fin.year)))
then
if month(inputSet[element]) = selectedMonth
then
newContacts = newContacts +1
end if
end if
Loop Until [end of inputSet]
End Function


such that a call to newContacts(contact,3) should return the same a
the nested functions described above. Alternatively, referring int
the function by newContacts(N:N,3) would work well enough (although b
less clear to other users).

Is this possible?

Many thanks in advance

 

[daddylonglegs]

Don't know about VBA but you could count the number of dates which fal
within a specific month and year using a single formula, e.g. for Apri
2006

=SUMPRODUCT(--(MONTH(contactResults)=4),--(YEAR(contactResults)=2006)

 

[Tom Ogilvy]

You could actually get your entire table built in one step by creating a
Pivot Table. Group on Year and Month

or

Using the Countif Formula you have already shown you can get the results
directly with no intermediate columns:

=Countif(A:A,">=3/1/2004")-countif(A:A,">3/21/2004")

will give you the count for March 2004. this will also calculate much
faster than the sumproduct formula suggested.

 

[Dexsquab]

Sumproduct seems to be for multiplying two or more equally sized
arrays.

I've tried a few combinations of the line you've suggested, and havent
been able to make it work.

I only have one array (a column) that needs to be searched and summed
across. To give a more concrete example:

[contact]
03/01/2001 FALSE
01/07/2001 FALSE
19/06/2004 FALSE
22/08/2004 FALSE
31/03/2005 FALSE
01/04/2005 TRUE 4
18/04/2005 TRUE 4
22/06/2005 TRUE 6
31/03/2006 TRUE 3
01/04/2006 FALSE
27/08/2006 FALSE

-The first column (labelled contact) is a list of dates.

-2nd column is a boolean generated by =AND($A2>38442, $A2<38808)
(used numbers as using other date references seemed to throw it out)

-3rd column is the month of contact, if it falls in the right year;
generated by
=IF(AND($A2>38442, $A2<38808), MONTH($A2), "") (as you can see, the
column 2 calculation is embedded in this function)

It is this list of numbers that becomes contactResults, such that
countif(contactResults,3) would return all the valid March contacts.

I seek a single function to look through the dates and find (for
example) only the March results in the year 2005.

 

[Dexsquab]

Thanks Tom, it's looking like the combination of two countifs wil
work.

Trying it out now.

Many thanks, again

 

[daddylonglegs]

Hi Dexsquab,

I'm sure Tom's right about COUNTIF being the better option.

Just for your information.....

Yes, the SUMPRODUCT formula does multiply equally sized arrays, it
works on the dates directly, so if your dates are in A1:A100 it would
simply be

=SUMPRODUCT(--(MONTH(A1:A100)=4),--(YEAR(A1:A100)=2006))

the two criteria produce arrays of TRUE/FALSE values which are then
coerced to 1/0 values and can therefore be multiplied and added, this
effectively gives you a count of when both conditions are true.

You could even shorten it to

=SUMPRODUCT(--(TEXT(A1:A100,"mmm-yy")="Apr-06"))