How would you go about summing these different columns?

Hello!

I would definately regard myself as a beginner when it comes to using Excel.
There will probably be a fairly simple solution to my problem, but I will be very grateful to anyone who helps me.
I am having problems with summing a large datasheet, and I have added an example here as an attachment.
There are many different cities, and products. Sometimes there will be 3 products, sometimes 10 or more depending on which product is being looked at.
Anyways, what I'm after is a way to sum this sheet and others like it in as simple way as possible.
The sheet will be updated monthly with new data, possibly adding new cities and products.
My way of doing this so far has been to manually sum up each "Paper Clip" cell, this should however turn out to be a bad method if new cities or products are updated.

So the question remains, how would you go about summing this up? Idealy the solution would allow me to type the formula in, and then drag it to the right then down.
I know a lot can be automated in Excel if you have the right knowledge, so I turn to the experts.

Which column do you need to sum? And what is your criteria for the sum? Do you just need to sum all rows beginning with paper clip for example? Does the criteria need to be dynamic?

Thank you for taking your time to answer.

Originally Posted by seasider89

Which column do you need to sum? And what is your criteria for the sum? Do you just need to sum all rows beginning with paper clip for example?
Does the criteria need to be dynamic?

I would like to columns G, H, I, J. So the ones in yellow should ideally be summed.
The "PAPER CLIP" "group" if you will consist of Product series 5444444, 9888888, 3222222.
So if i did it manually, G3 would be =G4+G5+G6+G7+G8+G9.
Is it possible to combine the Vlookup with this sum, stating something like if column B says just "PAPER CLIP" then sum the different product series stated above?
Or how would you automate this as smoothely as possible?

Originally Posted by Pepe Le Mokko

Pehaps a Pivot Table?

I was thinking that as well, but if I have understood it correctly, the pivot table is not too great if I want the data to update?
Or maybe I have misunderstood? The data will be updated monthly and is connected to a rather large database of numbers.

I think a PivotTable is by far your best option. You'll need to remove the row between the column headings and the data first, and if you're going to be updating regularly then I would recommend converting your data to a table.

The only thing you'll need to do is hit refresh all on the data tab when you've updated the data table and the pivot will update to reflect the changes.

Originally Posted by seasider89

I think a PivotTable is by far your best option. You'll need to remove the row between the column headings and the data first, and if you're going to be updating regularly then I would recommend converting your data to a table.

It would be great if you would show how the pivottable would sum up the paper clip header in the file! Because I don't really understand yet .

But then i guess it isn't possible to make Excel Sum just the specific rows that I want matched to the specific product numbers?
I feel as it would be something similar to Sumifs or Sumif, that could be combined with the vlookup forumla that I already have?
In text it, in my mind, says that take vlookup for everything except the the larger "paper clip" row, as this one is designated to sum up the different product series in each city?

I'm a little confused as to what the larger paper clip row is? Is this a subtotal row for a city, because if so the numbers don't add up. Also, what's the difference between the product numbers and product name.

I think your problem should be easily solvable but I'm struggling to get around how you've structured your data and how to suggest a solution that would incorporate new product lines.

I think the best bet is to mock up a manual sheet showing what it should be outputting.

Perhaps colour code your sheet to make it better to understand. For example, your inputs in one colour, formulas in another, output in another.

Thank you for all your input. I apologize for the misformed excel example, I realize that it was confusing now.
I have changed the file so that the sums should match each other. Here you will now see that the Total Paper Clip rows are built off the smaller groups.
I have also added a 50g paper clip product under one of the product series, so that it is understandable.
So each product series could have multiple products in them.

With this new information, is the best option still to use a pivot table? I see that the one pepe made was wrong because of my earlier bad excel file. Would it still work?
Is it even possible to do what i'm after, a formula that can be combined with the Vlookup in the rows G & H? In my mind this would be ideal.

And it probably should be said that the title isn't really correct as it is the rows that i would like to sum.

Would a SUMIF or SUMIFS work, combined with the Vlookup possibly?