Calculating Date Ranges
 

I have an Excel spreadsheet in which we use to keep track of online courses
we work on each semester at work. So far, I've been able to use the COUNTIF
function to calculate the number of hours it takes to work on a course using
a drop-down list that I've created.

I would like to add some additional data to my "Reports" tab that would
calculate the number of courses that we work on for a specific date range
(example: from January 1, 2007 to March 15, 2007).

Further, is there also a way to tally this information if I have multiple
tabs on my spreadsheet that contain similar data?

Calculating Date Ranges
 

I think I figured part of my answer out by searching the forums. I can try
to use:

=SUMPRODUCT(--(MONTH(M2:M1000)=4))

But...I'm still confused on how to get data from multiple tabs.

"Chris Hofer" wrote:

I have an Excel spreadsheet in which we use to keep track of online courses
we work on each semester at work. So far, I've been able to use the COUNTIF
function to calculate the number of hours it takes to work on a course using
a drop-down list that I've created.

I would like to add some additional data to my "Reports" tab that would
calculate the number of courses that we work on for a specific date range
(example: from January 1, 2007 to March 15, 2007).

Further, is there also a way to tally this information if I have multiple
tabs on my spreadsheet that contain similar data?

Calculating Date Ranges
 

I'm still confused on how to get data from multiple tabs.

One way:

Make a list of your sheet names. Say that list is in the range C1:C5

A1 = 1/1/2007
A2 = 3/15/2007

=SUMPRODUCT(COUNTIF(INDIRECT("'"&C1:C5&"'!M2:M1000 "),"="&A1))-SUMPRODUCT(COUNTIF(INDIRECT("'"&C1:C5&"'!M2:M1000" ),""&A2))

Biff

"Chris Hofer" wrote in message
...
I have an Excel spreadsheet in which we use to keep track of online courses
we work on each semester at work. So far, I've been able to use the
COUNTIF
function to calculate the number of hours it takes to work on a course
using
a drop-down list that I've created.

I would like to add some additional data to my "Reports" tab that would
calculate the number of courses that we work on for a specific date range
(example: from January 1, 2007 to March 15, 2007).

Further, is there also a way to tally this information if I have multiple
tabs on my spreadsheet that contain similar data?

Calculating Date Ranges
 

Earlier today I had found this on the forums:

EXAMPLE:
=SUMPRODUCT(--(MONTH(M2:M1000)=4))

This worked well for me when this formula was in the same tab as my data.
How would I go about calculating the number of April (4) entries in a column
that were in a different tab called "Fall 2007-08 (1st term)"?

"T. Valko" wrote:

I'm still confused on how to get data from multiple tabs.

One way:

Make a list of your sheet names. Say that list is in the range C1:C5

A1 = 1/1/2007
A2 = 3/15/2007

=SUMPRODUCT(COUNTIF(INDIRECT("'"&C1:C5&"'!M2:M1000 "),"="&A1))-SUMPRODUCT(COUNTIF(INDIRECT("'"&C1:C5&"'!M2:M1000" ),""&A2))

Biff

"Chris Hofer" wrote in message
...
I have an Excel spreadsheet in which we use to keep track of online courses
we work on each semester at work. So far, I've been able to use the
COUNTIF
function to calculate the number of hours it takes to work on a course
using
a drop-down list that I've created.

I would like to add some additional data to my "Reports" tab that would
calculate the number of courses that we work on for a specific date range
(example: from January 1, 2007 to March 15, 2007).

Further, is there also a way to tally this information if I have multiple
tabs on my spreadsheet that contain similar data?

Calculating Date Ranges
 

Just add the sheet name:

=SUMPRODUCT(--(MONTH('Fall 2007-08 (1st term)'!M2:M1000)=4))

Biff

"Chris Hofer" wrote in message
...
Earlier today I had found this on the forums:

EXAMPLE:
=SUMPRODUCT(--(MONTH(M2:M1000)=4))

This worked well for me when this formula was in the same tab as my data.
How would I go about calculating the number of April (4) entries in a
column
that were in a different tab called "Fall 2007-08 (1st term)"?

"T. Valko" wrote:

I'm still confused on how to get data from multiple tabs.

One way:

Make a list of your sheet names. Say that list is in the range C1:C5

A1 = 1/1/2007
A2 = 3/15/2007

=SUMPRODUCT(COUNTIF(INDIRECT("'"&C1:C5&"'!M2:M1000 "),"="&A1))-SUMPRODUCT(COUNTIF(INDIRECT("'"&C1:C5&"'!M2:M1000" ),""&A2))

Biff

"Chris Hofer" wrote in message
...
I have an Excel spreadsheet in which we use to keep track of online
courses
we work on each semester at work. So far, I've been able to use the
COUNTIF
function to calculate the number of hours it takes to work on a course
using
a drop-down list that I've created.

I would like to add some additional data to my "Reports" tab that would
calculate the number of courses that we work on for a specific date
range
(example: from January 1, 2007 to March 15, 2007).

Further, is there also a way to tally this information if I have
multiple
tabs on my spreadsheet that contain similar data?