How do I sum all totals for apples across worksheets?

  • Thread starter Thread starter Rob Howells
  • Start date Start date
R

Rob Howells

Hi All,

I'm new here and this is my first time at thinking about anything but
the simplest task in excel (97).

I have a workbook with 10 sheets at the moment, Jan to Oct, each has a
list of products with a total amount (£) sold i.e.


Bannanas 34.50

Apples 12.00

I need to be able to look through all sheets for, say Apples and sum
the total in the next column so I end up with the total (£) apples
between Jan and Oct?

Can anyone help point me in the right direction with this?

Regards

Rob Howells
 
You can do a sum over sheets the same way you do a sum on
one sheet.

On your New Sheet

=January!A2+February!A2... etc, where A2 is the cell with
the total apples for that month.
 
The easiest way:
=sumif(sheet1!a1:a20,"=Apples",sheet1!b1:b20)+sumif(sheet2!a1:a20,"=Apples",sheet2!b1:b20)+sumif(sheet3!a1:a20,"=Apples",sheet3!b1:b20)+.....etc.

Where a1:a20 is the range with Apples, Bananas, etc and b1:b20 is the
range of where the money amounts are. Put this formula on your summary
page.

Hope that helps!
Ed
 
Thanks Ed, seems just like what I was looking for. Out of interest
though is there a way to do this with VB?

Cheers

Rob
 
This is not code, and it may be a little too late for you if you've already
put a lot of work into formatting the previous 10 sheets, but if all the
sheets have the same form, this summing of values could be accomplished very
simply.

For example, if you made a monthly sheet with all the products included, and
then simply copied the blank (no values) sheet and changed the name to the
current month, a simple formula could display totals on a cover (main)
sheet.

That way, all sheets could have
A1 Apples
A2 Peaches
A3 Pears
etc ...
With the totals perhaps in B1, B2, B3 etc ...

Your cover (main) sheet could also be a copy as far as product placement in
the cells.
You could add a blank sheet at the last position of the WB and name it END.
On the cover sheet, in the totals column (B1) you could enter this formula:

=SUM(Jan:End!B1)

And copy it down as far as needed.

As you add each new sheet (month) in front of the blank END sheet, the
totals are automatically included in your sum formulation.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Thanks Ed, seems just like what I was looking for. Out of interest
though is there a way to do this with VB?

Cheers

Rob
 
Back
Top