Totalling Up Data From Multiple Excel Spreadsheets In A Single Workbook

**ThomasHH** · 02-04-2013, 12:47 PM

Greetings one and all,

I hope you can help with this basic problem, that sadly, due to my lack of knowledge...cannot seem to sort.

I have a feeling that it is a very easy thing to do, so hoping one of the experts with Excel can give me a simple way to sort this.
:-)

I have multiple spreadsheets within a single workbook, each sheet is a basically a different month of 2012.

Within these spreadsheets contains data under many columns about confidential client information. (Name, age, Risk level, etc...)

The main thing I need to gather is one set of data from a specific column (Risk Level) over this year's worth of spreadsheets.

The column title in question contains 1 of 4 'text' options, that I have typed in:
LOW, MEDIUM, HIGH and RISK

I need to know how to acquire the total for ALL these individual options and put them into a new spreadsheet, so I can see how many LOW's I have for the year in total, how many MEDIUMS, etc...

Sorry to bother you with this, but I'm hoping someone can guide me accordingly.
I vaguely remember something about macro's and formula's from school...

I may even undertake an Excel training course at this rate!

Regards,

Tom/Ben

**Special-K** · 02-04-2013, 01:08 PM

Try googling "Excel 3d referencing"

**FDibbins** · 02-04-2013, 01:13 PM

Hi Tom and welcome to the forum

I know you said your sheets contain sensitive info, so could you make up a dummy workbook so we can see what your data/layout etc looks like? otherwise we will just be guessing at suggestions for you

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**ThomasHH** · 02-05-2013, 05:47 AM

Many thanks for the replies, everyone.

I put together a dummy workbook to show what I mean...but my computer network won't let me upload the file to show you!

I guess I'll have to just try and work it out myself, but I'll have a look into 3D Referencing as a starting point.

Many thanks...and wish me luck!

Tom/Ben

**ThomasHH** · 02-05-2013, 06:17 AM

Ok, I've managed to get some images of my mock workbook to show you what I mean.

\1

This image is a basic mock-up of the workbook I'm using, only without all the other information in it.

I need to get the total amount of LOW, MEDIUM, HIGH and RISK from ALL the sheets (each month) and put them onto the TOTAL sheet, which is the image below and part of the same workbook at the moment.

\1

So, as you can see...I need a code/formula to take the amount of LOW's from each month/sheet and make the number appear in my TOTAL sheet, then the same for MEDIUM, then HIGH and finally, RISK.

Hope this make sense and someone can give me a clue as to how to get these totals!

Many thanks,

Ben