look for a range of values in a formula

[jlbetz]

I am creating a profit and loss statement, in my workbook sheet
contains all the raw data, sheet2 is the rollup into the P&L format.
am trying to come up with a formula that will look at a range of value
in column b (these are account numbers) and if the values are withi
the range then sum the values in column c (these are the totals for th
month). Also, the raw data is large because it spans 52 sub accounts.
The raw data spreadsheet looks like this: The acct # range from 6000 t
9999. Each line on the P&L sums various combinations of the acct#'s.

BU/Job# Acct jan-03 total feb-03 total etc....
50700 6010 $1,000.00 $2,5000.00
50752 6095 $3,250.00 $5,650.00

I have tried the following:

Sumif('2003 actuals.xls'!linkedarea2,"6020",'200
actuals.xls'!linkedarea3)

this worked great since I am asking it to look for a single value, bu
if I try

Sumif('2003 actuals.xls'!linkedarea2,"6000:6089",'200
actuals.xls'!linkedarea3)

it doesn't work. The formula returns the sum of everything

 

[J.E. McGimpsey]

One way:

=SUMPRODUCT(--('2003 actuals.xls'!linkedarea2>=6000), --('2003
actuals.xls'!linkedarea2<=6089), '2003 actuals.xls'!linkedarea3)

 

[jlbetz]

Okay that formula worked great, but I have tried to modify it for
multiple ranges and a single value, and can't get it to work, here is
what I have.

=SUMPRODUCT(--('2003 10300 Actuals.xls'!LinkedArea2>="6270"),--('2003
10300 Actuals.xls'!LinkedArea2<="6289"),--('2003 10300
Actuals.xls'!LinkedArea2>="6300"),--('2003 10300
Actuals.xls'!LinkedArea2<="6360"), '2003 10300
Actuals.xls'!LinkedArea3)+SUMIF('2003 10300
Actuals.xls'!LinkedArea2,"6961",'2003 10300 Actuals.xls'!LinkedArea3)

Any help would be greatly appreciated.