software

I am using sumif to add data from about 12 worksheets onto one total
sheet.
my range d17-d38 on multiple sheets
criteriaquot;=103quot;
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: www.hightechtalks.com/m598
View this thread: www.hightechtalks.com/t2317219Excel 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(quot;'quot;amp;H1:H12amp;quot;'!D1738quot;) ,103,INDIRECT(quot;'quot;amp;H1:H12amp;quot;'!G17:G38quot;)))

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)quot;JJacksonquot; gt; wrote in message
...
gt;
gt; I am using sumif to add data from about 12 worksheets onto one total
gt; sheet.
gt; my range d17-d38 on multiple sheets
gt; criteriaquot;=103quot;
gt; sum_range is g17-g38
gt; When I put this in I get a #Value error and I cant seem to figure out
gt; why.
gt; If I do a single sheet at a time and put a sign between each sumif
gt; formula it will work but it wont work when I do multiple sheet ranges.
gt;
gt; Any help would be greatly appreciated.
gt; Thanks
gt; Jaye
gt;
gt;
gt; --
gt; JJackson
gt; ------------------------------------------------------------------------
gt; JJackson's Profile: www.hightechtalks.com/m598
gt; View this thread: www.hightechtalks.com/t2317219
gt;
Im guessing it should look something like this
=SUMPRODUCT(SUMIF(INDIRECT(quot;quot;quot;sheet1:sheet13quot;quot;!D17 :38quot;),103,INDIRECT
(quot;'quot;sheet1:sheet13quot;'!m17:m38quot;)))
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: www.hightechtalks.com/m598
View this thread: www.hightechtalks.com/t2317219No, 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(quot;'quot;amp;H1:H12amp;quot;'!D1738quot;) ,103,INDIRECT(quot;'quot;amp;H1:H12amp;quot;
'!G17:G38quot;)))--

HTH

RP
(remove nothere from the email address if mailing direct)quot;JJacksonquot; gt; wrote in message
...
gt;
gt; Im guessing it should look something like this
gt; =SUMPRODUCT(SUMIF(INDIRECT(quot;quot;quot;sheet1:sheet13quot;quot;!D17 :38quot;),103,INDIRECT
gt; (quot;'quot;sheet1:sheet13quot;'!m17:m38quot;)))
gt; if that is what it is supposed to look like I am still getting an error
gt; it says invalid cell ref.
gt; Thanks for your help with this.
gt;
gt;
gt; --
gt; JJackson
gt; ------------------------------------------------------------------------
gt; JJackson's Profile: www.hightechtalks.com/m598
gt; View this thread: www.hightechtalks.com/t2317219
gt;

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(quot;'quot;amp;M-DEBEAU:B-HUTSON:W-DEBEAU:R-ELLER:N-CARASCO
JR:N-CARASCO
SR:B-COTTON-DECKER:L-GREEN:P-ROLAND:T-THOMPSON:E-GALLEGOS:M-GOINGS:M-ARAGON:E-CARASCO:L-MITCHELL:WARDamp;quot;'!D1738quot;),103,INDIRECT(quot;'quot;amp;M-DEBEAU:B-HUTSON:W-DEBEAU:R-ELLER:N-CARASCO
JR:N-CARASCO SR:B-COTTON-DECKER:L-GREEN:P-ROLAND:T-THOMPSON:E-
GALLEGOS:M-GOINGS:M-ARAGON:E-CARASCO:L-MITCHELL:WARDamp;quot;'!M17:M38quot;)))

Thanks again for all your help...--
JJackson
------------------------------------------------------------------------
JJackson's Profile: www.hightechtalks.com/m598
View this thread: www.hightechtalks.com/t2317219Hi!

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

You're not quot;listeningquot; !!!

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(quot;'quot;amp;H1:H17amp;quot;'!D1738quot;) ,103,INDIRECT(quot;'quot;amp;H1:H17amp;quot;
'!M17:M38quot;)))

Biff

quot;JJacksonquot; gt; wrote in message
...
gt;
gt; I still couldnt get it to work. This is the actual formula off my sheet
gt; maybe you can see the error I am missing.
gt;
gt; =SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;M-DEBEAU:B-HUTSON:W-DEBEAU:R-ELLER:N-CARASCO
gt; JR:N-CARASCO
gt; SR:B-COTTON-DECKER:L-GREEN:P-ROLAND:T-THOMPSON:E-GALLEGOS:M-GOINGS:M-ARAGON:E-CARASCO:L-MITCHELL:WARDamp;quot;'!D1738quot;),103,INDIRECT(quot;'quot;amp;M-DEBEAU:B-HUTSON:W-DEBEAU:R-ELLER:N-CARASCO
gt; JR:N-CARASCO SR:B-COTTON-DECKER:L-GREEN:P-ROLAND:T-THOMPSON:E-
gt; GALLEGOS:M-GOINGS:M-ARAGON:E-CARASCO:L-MITCHELL:WARDamp;quot;'!M17:M38quot;)))
gt;
gt; Thanks again for all your help...
gt;
gt;
gt; --
gt; JJackson
gt; ------------------------------------------------------------------------
gt; JJackson's Profile: www.hightechtalks.com/m598
gt; View this thread: www.hightechtalks.com/t2317219
gt;