SUMIF on multiple sheets

J

JJackson

I am using sumif to add data from about 12 worksheets onto one tota
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 ou
why.
If I do a single sheet at a time and put a + sign between each sumi
formula it will work but it wont work when I do multiple sheet ranges

Any help would be greatly appreciated.
Thanks
Jay
 
P

Peo Sjoblom

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

JJackson

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 erro
it says invalid cell ref.
Thanks for your help with this
 
B

Bob Phillips

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

JJackson

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

=SUMPRODUCT(SUMIF(INDIRECT("'"&M-DEBEAU:B-HUTSON:W-DEBEAU:R-ELLER:N-CARASC
JR:N-CARASC
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-CARASC
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..
 
B

Biff

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
 
Top