Sumif from multiple worksheets

  • Thread starter Thread starter Donna
  • Start date Start date
D

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.
 
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
 
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.
 
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")))
Click to expand...

Then you'll need an array of worksheet names.

=SUMPRODUCT(SUMIF(INDIRECT("'"&{"Retail","Mailorder","Shop"}&"'!A1:A3"),
Criteria,INDIRECT("'"&{"Retail","Mailorder","Shop"}&"'!B1:B3")))
 
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
 
Back
Top