Calculations with date fields
 

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

Calculations with date fields
 

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:K1 47="V"))
to return the referrals in Mar08 for area V
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Cassidy1" wrote:
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

Calculations with date fields
 

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


--
Biff
Microsoft Excel MVP


"Cassidy1" wrote in message
...
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

Calculations with date fields
 

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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).


--
Biff
Microsoft Excel MVP


"Cassidy1" wrote in message
...
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

Calculations with date fields
 

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

Cassidy1

"Max" wrote:

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:K1 47="V"))
to return the referrals in Mar08 for area V
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Cassidy1" wrote:
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

Calculations with date fields
 

Pleasure`. Glad it helped.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Cassidy1" wrote in message
...
Excellent! This worked - thanks very much for your help.

Calculations with date fields
 

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.

"Max" wrote:

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:K1 47="V"))
to return the referrals in Mar08 for area V
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Cassidy1" wrote:
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

Calculations with date fields
 

Check your Wiindows Regional Settings (in Control Panel).
--
David Biddulph

"Cassidy1" wrote in message
...
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.

"Max" wrote:

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:K1 47="V"))
to return the referrals in Mar08 for area V
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Cassidy1" wrote:
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

Calculations with date fields
 

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?

"David Biddulph" wrote:

Check your Wiindows Regional Settings (in Control Panel).
--
David Biddulph

"Cassidy1" wrote in message
...
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.

"Max" wrote:

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:K1 47="V"))
to return the referrals in Mar08 for area V
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Cassidy1" wrote:
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

Calculations with date fields
 

It'll change the date format of how things are displayed in places like
Windows Explorer, but I assumed that you wanted to set things up the same
way they had been on your previous machine?
--
David Biddulph

"Cassidy1" wrote in message
...
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?

"David Biddulph" wrote:

Check your Wiindows Regional Settings (in Control Panel).
--
David Biddulph

"Cassidy1" wrote in message
...
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.

"Max" wrote:

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:K1 47="V"))
to return the referrals in Mar08 for area V
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Cassidy1" wrote:
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