sums using multiple worksheets

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Sums using multiple worksheets

Sums using multiple worksheets

resolvedResolved · Urgent Priority · Version 2003

Liz has attended:
SharePoint course

Sums using multiple worksheets

Hello

If I have a workbook in which the same format is repeated on many worksheets, have do I tell excel to create a summary page, e.g. add the contents of cell A1 on all the worksheets and place it in A1 on the summary page.

Some of the functions are simple addition.

Others will need to count the number of Yesses and Noes.

Please send idiot's guide, thank you

RE: sums using multiple worksheets

Hi Liz

On your summary page click in the first cell and begin your formula with

=sum(

click on the first cell on the first sheet containing data
put your finger on shift
click on the same cell in the last sheet containing data

type the close bracket

)

and press enter

Excel should now create a sum function that sums the values in the cell spanning the sheets in your workbook .

I hope this helps - let us know if you have any further questions.

Kind regards,

Andrew



Kind regards,
Andrew

RE: sums using multiple worksheets

Thanks, thats great for the additions.
I've been trying to do the same but using COUNTIF (to tot up how many "Y" there are for example). Not got it right yet, please help
Liz

RE: sums using multiple worksheets

Hi Liz

You'll find that not all functions support this feature but COUNT (which counts the number of cells with a numeric value) and COUNTA (which counts the number of non blank cells) might get the answer you are looking for.

Kind regards,
Andrew


 

Excel tip:

Hiding a worksheet in Excel

Want to circulate an Excel workbook file but keep certain worksheets hidden from your colleagues' view?

You can do so by bringing up the sheet you wish to hide on your screen; then going to Format - Sheet - Hide.

It will not be immediately obvious that a sheet is hidden from view unless perhaps the sheet are still labelled Sheet 1, Sheet 2 etc.

To display the sheet again, you can go to Format - Sheet - Unhide on any of the other sheets in the workbook. A dialogue box will appear, allowing you to select the hidden sheet/s. Click OK to make the sheet/s reappear again.

View all Excel hints and tips


Server loaded in 0.08 secs.