handling range of data from the middle with sumproduct

[baseball]

I have a table of yearly statistics for baseball teams from 1876 through 2001. Each record is the data for a specific team in a specific year, and the several fields in a record show the number of home runs, the number of strikeouts, etc., that the team accumulated for that season.

It's simple to extract the data for a single year using the sumproduct formula, and if I rewrite the formula, I can get all the data from a certain forward or all data before that year.

=sumproduct((A3:A1000=1995)*(B3:B1000="Team")*(C2:C1000)) gets data from 1995 for the variable "Team"
=sumproduct((A3:A1000>=1995)*(B3:B1000="Team")*(C2:C1000)) gets it from 1995 through 2001
=sumproduct((A3:A1000<=1995)*(B3:B1000="Team")*(C2:C1000)) gets it from 1876 through 1995

The problem is that the formulas must be changed, which is tedious, and there is no way that I can think of to handle a range of years from the middle, e.g.. 1945-1958. Is there some way to do this with sumproducts and maybe named ranges (FirstYear, LastYear)?

Thanks,
Cliff

 

[Aladin Akyurek]

On 2002-09-06 08:35, baseball wrote:
I have a table of yearly statistics for baseball teams from 1876 through 2001. Each record is the data for a specific team in a specific year, and the several fields in a record show the number of home runs, the number of strikeouts, etc., that the team accumulated for that season.

It's simple to extract the data for a single year using the sumproduct formula, and if I rewrite the formula, I can get all the data from a certain forward or all data before that year.

=sumproduct((A3:A1000=1995)*(B3:B1000="Team")*(C2:C1000)) gets data from 1995 for the variable "Team"
=sumproduct((A3:A1000>=1995)*(B3:B1000="Team")*(C2:C1000)) gets it from 1995 through 2001
=sumproduct((A3:A1000<=1995)*(B3:B1000="Team")*(C2:C1000)) gets it from 1876 through 1995

The problem is that the formulas must be changed, which is tedious, and there is no way that I can think of to handle a range of years from the middle, e.g.. 1945-1958. Is there some way to do this with sumproducts and maybe named ranges (FirstYear, LastYear)?

Thanks,
Cliff

=SUMPRODUCT((A3:A1000>=E1)*(A3:A1000<=E2)*(B3:B1000="Team")*(C2:C1000))

where E1 houses a year of interest and E2 another with E1 < E2.

 

[baseball]

On 2002-09-06 08:43, Aladin Akyurek wrote:

On 2002-09-06 08:35, baseball wrote:
I have a table of yearly statistics for baseball teams from 1876 through 2001. Each record is the data for a specific team in a specific year, and the several fields in a record show the number of home runs, the number of strikeouts, etc., that the team accumulated for that season.

It's simple to extract the data for a single year using the sumproduct formula, and if I rewrite the formula, I can get all the data from a certain forward or all data before that year.

=sumproduct((A3:A1000=1995)*(B3:B1000="Team")*(C2:C1000)) gets data from 1995 for the variable "Team"
=sumproduct((A3:A1000>=1995)*(B3:B1000="Team")*(C2:C1000)) gets it from 1995 through 2001
=sumproduct((A3:A1000<=1995)*(B3:B1000="Team")*(C2:C1000)) gets it from 1876 through 1995

The problem is that the formulas must be changed, which is tedious, and there is no way that I can think of to handle a range of years from the middle, e.g.. 1945-1958. Is there some way to do this with sumproducts and maybe named ranges (FirstYear, LastYear)?

Thanks,
Cliff

=SUMPRODUCT((A3:A1000>=E1)*(A3:A1000<=E2)*(B3:B1000="Team")*(C2:C1000))

where E1 houses a year of interest and E2 another with E1 < E2.

Thanks. That was quick...and easy. I was trying to use the AND function in conjunction with sumproduct, but I didn't think of doing it your way.

Thanks again,
Cliff