Current month question

B

Brandy

My data appears as such:

1/2/2004

It must stay in this format.

The information I need is how many referrals were made
only for the month of July using this information.

Any ideas on how I might accomplish this?
 
J

JulieD

Hi Brandy

try
=SUMPRODUCT((TEXT(A2:A27,"mmm")="Jul")*1)
OR
=SUMPRODUCT(--(TEXT(A2:A27,"mmm")="Jul"))
OR
=SUMPRODUCT(--(TEXT(A2:A27,"mmm")=TEXT(NOW(),"mmm")))

where A2:A27 contains your dates

Cheers
JulieD
 
D

Domenic

Hi Brandy,

Assuming that your dates are contained in Column A, and each date
represents a referral:

=SUMPRODUCT(--(MONTH(A1:A100)=7),--(YEAR(A1:A100)=2004))

Hope this helps!
 
N

Norman Harker

Hi Brandy!

Try:

=SUMPRODUCT(($A$1:$A$200<>"")*(TEXT($A$1:$A$200,"mmm")="Jul"))

I have to adjust for the possibility of blanks to cover you wanting
January because they are treated as 0 and the Date Serial number of 0
is treated as Sat, 1 Jan 1900 and is thus in the January total. I'de
used a cell reference for the short month name and it is not difficult
to enter Jan in I1 and then copy down (pressing Ctrl) to get the 12
months of data. However, a pivot table might be the prefered solution
for that.

Note that if the data includes different years, the formula will
return July total for all years.
 
F

Frank Kabel

Hi Julie

I would use the MONTH function :)
So to add an alternative:

=SUMPRODUCT(--(MONTH(A2:A27)=MONTH(NOW())))
 
J

JulieD

Hi Frank

thought about that one too but i had a document open using the TEXT function
(and it worked) so i just copied & pasted that :)

Cheers
JulieD
 
Top