formula problem

[REGGIE CARSON]

i have a speadsheet i'm working on that has three separate
areas of data on sheet one...on sheet two i have an area
to compile results of formulas..my proplem is with =sumif

= SUMIF(Sheet1!B8:B39,1,Sheet1!E8:E39)

i have non continuos ranges for my range and sum range..

the formula works with a single range put when i try to
put in my other non continuos ranges i get "formula
contains an error"

= SUMIF(Sheet1!B8:B39,Sheet1!B50:B82,Sheet1!
B95:B127,1,Sheet1!E8:E39,Sheet1!E50:E82,Sheet1!E95:E127)

 

[Bob Phillips]

Reggie,

Why don't you just add up the separate ranges, like so

=
SUMIF(Sheet1!B8:B39,1,Sheet1!E8:E29)+SUMIF(Sheet1!B50:B82,1,Sheet1!E50:E82)+
SUMIF(Sheet1!B95:B127,1,Sheet1!E95:E127)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[reggie]

bob that worked great.. i guess it was too simple,
thanks...
but there are times where i may need to change the
criteria entry..is there a way to change all three at one
time

 

[Frank Kabel]

Hi reggie
repalce the criteria with a cell reference. e.g.

=SUMIF(Sheet1!B8:B39,A1,Sheet1!E8:E29)+SUMIF(Sheet1!B50:B82,A1,Sheet1!E
50:E82)+SUMIF(Sheet1!B95:B127,A1,Sheet1!E95:E127)
where cell A1 stores your value

Frank