Excel Question - Summing Up Data?

Question

Hi all,

I have the following issue and I was wondering if someone out there could help me on this.

Imagine that you have an Excel spreadsheet that has 19 tabs (sheet1, sheet2, etc right through to 19) and then one final tab named summary.
Sheet1 - 19 containes mobile phone data with column a stating the type of call e.g. national, premium rate, international, etc and column b the cost.

In the summary section I want to sum up these headings from Sheet1 - 19, however there is a slight issue in that if I want to sum up the totals for International, there are various headers for this i.e. International - Zone 1, International - Zone 2 etc up to zone 10. In the summary section all I want is to name this part "International Calls".
I guess I need to combine a 'sumif' statement with a 'left' but having not much luck with this.
Is there any way this could be done using a simple formula?

Your help is very much appreicated.

Answer 1

Little known, but very powerful feature in Sumif is the ability to use a wildcard eg...

I have had to split this formula onto four lines but you don't type it that way:

=SUMIF(
$A$2:$A$4,
"International*",
B2:B4)

the * means anything starting with * is a match.

I think that should solve your problem for you.

Answer 2

I assume that if I call international zone1 there will be a charge in zone 1 only. If I call zone8 there will be a charge in zone8 only.
So don't try to be clever. Leave that to Excel.
As long as the columns for the different zones are together just sum all the values.
=sum(sheet1!A1:C4) or whatever you want to total.
That is all you need.

Answer 3

You could use a simple one like this but it could get long...
Sum(sheet1!b3+sheet2!b5+sheet3!c4) etc.. The easiest way to do it would be to sum all the columns in your tabbed sheets then reference those cells. Just make sure you use the ! after the sheet name.

Answer 4

Case 1 (values in different columns in each sheet):
simply you can add the values by using + sign & adding the values from each column by going to each sheet

Case 2 (values in single column)
mark the whole area of data values & use Subtotals menu option by going to Data -> Subtotals -> [define the column to group & use the sum function]

Answer 5

I have a list of data in different cells, i.e."items" and would like the lower most to be a compilation of "total items" of the compiled from the individual "items" listed above. How do I do this??

Answer 6

Try looking at http://www.mrexcel.com good resource and forum.

Hope this helps.

Thanks
David

http://www.shreddershop.com
Is your life in shreds?

Answer 7

just use the help in excel