Sumif from multiple worksheets

[Donna]

Tried to do a sumif from multiple worksheets but will not
work.

Here's my basic formula below.

SUMIF((Sheet2!A1:A3,Sheet3!A1:A3),Sheet1!A1,(Sheet2!
B1:B3,Sheet3!B1:B3))

Is it my formula or a limitation of the SUMIF function?
Any suggestions on how to sum fom multiple worksheets
based on a single criteria? Thanks in advance for any
expert advice.

 

[Peo Sjoblom]

One way, hopefully you didn't give a simplified example since it would be
rather different if you have
other sheet names..

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("2:3"))&"'!A1:A3"),Sheet1!A
1,INDIRECT("'Sheet"&ROW(INDIRECT("2:3"))&"'!B1:B3")))

that means you can't use sumif direct over multiple pages

 

[Donna]

Sorry, but in fact I do need to incorporate the real
worksheet names that represent our business groupings.
We use Retail, Mailorder, Shop for the worksheet names.

Not sure how this would incorporate into the formula you
provided. Please help.

 

[Harlan Grove]

Sorry, but in fact I do need to incorporate the real
worksheet names that represent our business groupings.
We use Retail, Mailorder, Shop for the worksheet names. ...
&"'!A1:A3"),Sheet1!A1,INDIRECT("'Sheet"&ROW(INDIRECT("2:3"))&"'!B1:B3")))

Then you'll need an array of worksheet names.

=SUMPRODUCT(SUMIF(INDIRECT("'"&{"Retail","Mailorder","Shop"}&"'!A1:A3"),
Criteria,INDIRECT("'"&{"Retail","Mailorder","Shop"}&"'!B1:B3")))

 

[Peo Sjoblom]

See, if you would have provided these names at once I wouldn't have to do
this twice!
And it would have helped if you said which one was the equivalent of the
other, what does Sheet1A1 stand for?

=SUMPRODUCT(SUMIF(INDIRECT("'"&{"Retail";"Mailorder";"Shop"}&"'!A1:A3"),Shee
t1!A1,INDIRECT("'"&{"Retail";"Mailorder";"Shop"}&"'!B1:B3")))

if the criteria is in sheet1 and the 3 sheets are supposed to be together
see above

If Retail!A1 = Sheet1!A1

=SUMPRODUCT(SUMIF(INDIRECT("'"&{"Mailorder";"Shop"}&"'!A1:A3"),Retail!A1,IND
IRECT("'"&{"Mailorder";"Shop"}&"'!B1:B3")))

that should give you enough meat to finish this.. Just replace the criteria
sheet and cell (this case Retail!A) with one of the other and
do the same for the {"Mailorder";"Shop"} part in both occasions