SUMif or SUMproduct across multiple worksheets?

[Eric Shamlin]

I have two seprate but related questions. I have a few excel workbooks with
multiple worksheets (nearly 30 worksheets in one of them). Each of the
workbooks has a summary page. Here are my two SUM related issues:

1) I need to add values in the same cell across the multiple sheets... is
there a way to do this without typing a gigantic formula:
ex =sum(sheet!A1,Sheet2!A1,Sheet!A1.....Sheet30!A1)
Basically, is there shorthand for adding across all or many worksheets?

2) One of the workbooks has each month split onto a separate sheet. I need
to be able to query across the sheets and sum items based on certain
criteria. The items are not on identical rows... but they are on identical
columns.

ex
Worksheet 2 (February)
Widget1, Price3, QuantityC

Worksheet 5 (May)
Widget1, Price2, QuantityG
Widget3, Price1, QuantityH

Worksheet 7 (July)
Widget1, Price2, QuantityZ
Widget2, Price4, QuantityG
Widget3, Price6, QuanitityY

I need to be able, on my summary page, query all worksheets and find line
items that include Widge1 and then add all related prices or all related
quantities. Or, find all widget1 items that ALSO were at Price2... and add
those Quanitities.

Again, not haveing to type in a super long formula would be great.
Hope that makes sense.

 

[Roger Govier]

Hi Eric

The first part is very easy.
=SUM(Sheet1:Sheet30!A1)

Personally, I prefer setting up two extra blank sheets, naming them Start
and End. Then drag these to include the range of sheets you want to sum and
on your summary sheet (which must not be within Start and End) enter
=SUM(Start:End!A1)

Now the single formula can be used to sum as few or as many sheets as you
require.If you want, once you have dragged Start to between your Summary
sheet and Sheet1, you can Hide it. Then all you need to do is drag End to
beyond the last sheet you wish to sum.

The second part can be done but having data for different months on
different sheets makes it more difficult. I am assuming that you have dates
for each transaction within each monthly sheet. Would you not be able to
copy all of the data to a single sheet? Then you could use a Pivot Table to
summarise by month, and by any of the criteria you wish.

For help on Pivot Tables take a look at
http://peltiertech.com/Excel/Pivots/pivotstart.htm
and
http://www.contextures.com/tiptech.html


Regards

Roger Govier