formula problem

R

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

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

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
 
F

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top