I am using sumif to add data from about 12 worksheets onto one total
sheet.
my range d17-d38 on multiple sheets
criteria"=103"
sum_range is g17-g38
When I put this in I get a #Value error and I cant seem to figure out
why.
If I do a single sheet at a time and put a + sign between each sumif
formula it will work but it wont work when I do multiple sheet ranges.

Any help would be greatly appreciated.
Thanks
Jaye


--
JJackson
------------------------------------------------------------------------
JJackson's Profile: http://www.hightechtalks.com/m598
View this thread: http://www.hightechtalks.com/t2317219

Excel has very limited multi sheet functions, sumif does not work although
you can get around it but you need to put a list of all sheets in question
in a range, assume that range is H1:H12 now you need to put the sheet names
of ALL your sheet there then you can use

=SUMPRODUCT(SUMIF(INDIRECT("'"&H1:H12&"'!D17:D38") ,103,INDIRECT("'"&H1:H12&"'!G17:G38")))

so first type the names of all sheets in H1:H12 (or wherever you want to but
then replace H1:H12 with your range) then apply the formula

--
Regards,

Peo Sjoblom

(No private emails please)


"JJackson" wrote in message
...

I am using sumif to add data from about 12 worksheets onto one total
sheet.
my range d17-d38 on multiple sheets
criteria"=103"
sum_range is g17-g38
When I put this in I get a #Value error and I cant seem to figure out
why.
If I do a single sheet at a time and put a + sign between each sumif
formula it will work but it wont work when I do multiple sheet ranges.

Any help would be greatly appreciated.
Thanks
Jaye


--
JJackson
------------------------------------------------------------------------
JJackson's Profile: http://www.hightechtalks.com/m598
View this thread: http://www.hightechtalks.com/t2317219

Im guessing it should look something like this
=SUMPRODUCT(SUMIF(INDIRECT("""sheet1:sheet13""!D17 :38"),103,INDIRECT
("'"sheet1:sheet13"'!m17:m38")))
if that is what it is supposed to look like I am still getting an error
it says invalid cell ref.
Thanks for your help with this.


--
JJackson
------------------------------------------------------------------------
JJackson's Profile: http://www.hightechtalks.com/m598
View this thread: http://www.hightechtalks.com/t2317219

No, you should enter each sheet name in a range and use that range, Peo's
formula is not using a start and end name, but requires each sheet name,
hence the range.

So enter Sheet1 in H1, Sheet2 in H2, etc, and then use the formula that Peo
gave you

=SUMPRODUCT(SUMIF(INDIRECT("'"&H1:H12&"'!D17:D38") ,103,INDIRECT("'"&H1:H12&"
'!G17:G38")))


--

HTH

RP
(remove nothere from the email address if mailing direct)


"JJackson" wrote in message
...

Im guessing it should look something like this
=SUMPRODUCT(SUMIF(INDIRECT("""sheet1:sheet13""!D17 :38"),103,INDIRECT
("'"sheet1:sheet13"'!m17:m38")))
if that is what it is supposed to look like I am still getting an error
it says invalid cell ref.
Thanks for your help with this.


--
JJackson
------------------------------------------------------------------------
JJackson's Profile: http://www.hightechtalks.com/m598
View this thread: http://www.hightechtalks.com/t2317219

I still couldnt get it to work. This is the actual formula off my sheet
maybe you can see the error I am missing.

=SUMPRODUCT(SUMIF(INDIRECT("'"&M-DEBEAU:B-HUTSON:W-DEBEAU:R-ELLER:N-CARASCO
JR:N-CARASCO
SR:B-COTTON:D-DECKER:L-GREEN:P-ROLAND:T-THOMPSON:E-GALLEGOS:M-GOINGS:M-ARAGON:E-CARASCO:L-MITCHELL:WARD&"'!D17:D38"),103,INDIRECT("'"&M-DEBEAU:B-HUTSON:W-DEBEAU:R-ELLER:N-CARASCO
JR:N-CARASCO SR:B-COTTON:D-DECKER:L-GREEN:P-ROLAND:T-THOMPSON:E-
GALLEGOS:M-GOINGS:M-ARAGON:E-CARASCO:L-MITCHELL:WARD&"'!M17:M38")))

Thanks again for all your help...


--
JJackson
------------------------------------------------------------------------
JJackson's Profile: http://www.hightechtalks.com/m598
View this thread: http://www.hightechtalks.com/t2317219

Hi!

Let me ask you a question. Does the formula you posted below look anything
like what Peo and Bob have suggested?

You're not "listening" !!!

Put the names of your sheets in a range of cells:

H1 = M-DEBEAU
H2 = B-HUTSON
H3 = W-DEBEAU
...
H17 = WARD

=SUMPRODUCT(SUMIF(INDIRECT("'"&H1:H17&"'!D17:D38") ,103,INDIRECT("'"&H1:H17&"
'!M17:M38")))

Biff

"JJackson" wrote in message
...

I still couldnt get it to work. This is the actual formula off my sheet
maybe you can see the error I am missing.

=SUMPRODUCT(SUMIF(INDIRECT("'"&M-DEBEAU:B-HUTSON:W-DEBEAU:R-ELLER:N-CARASCO
JR:N-CARASCO
SR:B-COTTON:D-DECKER:L-GREEN:P-ROLAND:T-THOMPSON:E-GALLEGOS:M-GOINGS:M-ARAGON:E-CARASCO:L-MITCHELL:WARD&"'!D17:D38"),103,INDIRECT("'"&M-DEBEAU:B-HUTSON:W-DEBEAU:R-ELLER:N-CARASCO
JR:N-CARASCO SR:B-COTTON:D-DECKER:L-GREEN:P-ROLAND:T-THOMPSON:E-
GALLEGOS:M-GOINGS:M-ARAGON:E-CARASCO:L-MITCHELL:WARD&"'!M17:M38")))

Thanks again for all your help...


--
JJackson
------------------------------------------------------------------------
JJackson's Profile: http://www.hightechtalks.com/m598
View this thread: http://www.hightechtalks.com/t2317219