Calculations with date fields

[Cassidy1]

Hi,
I have an excel spreadsheet that contains info on referrals; date received,
area received, client file number etc.
I need to figure out how to track monthly referrals by area.
I already have a column that calculates total referrals each month that
looks like this: =COUNTIF(J2:J147,">31/03/2008") and this works great, but I
have another column that shows the area referred: V for Victoria or S for
Sidney and I want to know how many referrals I received each month for each
area, so any help on how I would formulate that?

Thanks

 

[Max]

Assuming referral dates in col J (dates are assumed real dates),
areas in col K (V, S etc),
you could try something like this:
=SUMPRODUCT((TEXT(J2:J147,"mmmyy")="Mar08")*(K2:K147="V"))
to return the referrals in Mar08 for area V

 

[T. Valko]

Do you need to include only months from specifc years?

These will include months from *any* year:

=SUMPRODUCT(--(MONTH(J2:J147)=n),--(K2:K147="V"))
=SUMPRODUCT(--(MONTH(J2:J147)=n),--(K2:K147="S"))

Where n = month number: Jan=1, Feb=2, Mar=3, etc

Note that if a cell in the date range is empty it will evaluate as month 1.
So, you may need to test that there are no empty cells in the date range:

=SUMPRODUCT(--(J2:J147<>""),--(MONTH(J2:J147)=n),--(K2:K147="V"))
=SUMPRODUCT(--(J2:J147<>""),--(MONTH(J2:J147)=n),--(K2:K147="S"))

If you need to include only months from a specific year:

=SUMPRODUCT(--(MONTH(J2:J147)=n),--(YEAR(J2:J147=yn),--(K2:K147="V"))
=SUMPRODUCT(--(MONTH(J2:J147)=n),--(YEAR(J2:J147=yn),--(K2:K147="S"))

Where yn = year number such as 2008. Using this method eliminates the need
to test for empty cells in the date range (unless you're testing for the
year 1900).

 

[T. Valko]

Ooops! Typo:

=SUMPRODUCT(--(MONTH(J2:J147)=n),--(YEAR(J2:J147=yn),--(K2:K147="V"))
=SUMPRODUCT(--(MONTH(J2:J147)=n),--(YEAR(J2:J147=yn),--(K2:K147="S"))

I left out the closing ) in the YEAR function. Should be:

=SUMPRODUCT(--(MONTH(J2:J147)=n),--(YEAR(J2:J147)=yn),--(K2:K147="V"))
=SUMPRODUCT(--(MONTH(J2:J147)=n),--(YEAR(J2:J147)=yn),--(K2:K147="S"))

 

[Cassidy1]

Excellent! This worked - thanks very much for your help.

Cassidy1

 

[Max]

Pleasure`. Glad it helped.

 

[Cassidy1]

Hi Max, since you helped me with this calculation, I have gotten a new
computer and had all my programs and documents etc copied over to it. For
some reason, in excel, all my date formats were changed from dd/mm/yyyy to
mm/dd/yyyy and my date calculations are not working now. I have gone in to
"format cells" and set the format to custom which changed my date fields back
to dd/mm/yyyy, but my calculations still don't work.

 

[David Biddulph]

Check your Wiindows Regional Settings (in Control Panel).

 

[Cassidy1]

I'm not a real computer whiz so I have to ask this - won't this change the
date format of everything on my computer?