lookup a range of data then sum totals on another sheet in order

Hi All,

I have a spreadsheet that I would like some help with.
I have a range of data that relates to an order and the amount for that order.
I would like to somehow sum the order amounts and show the total on another sheet, but also split them depending on the category.

e.g. Sheet 1 shows the data I start with (always copied from another workbook) column A is the order name column B is the ordered amount.
sheet 2 shows the categories in bold with the corresponding order names of the category they belong in, ( columns E to I)

I would like to be able to look up the names within the category (from sheet 1), sum the total and put the answer into column B next to the category name in column A.

I have had some experience with formulas, but looking up values and summing them has me puzzled.

I appreciate any help on this

Thanks

XOR,
Thanks for your reply, the differences in the names was an oversight on my part, sorry.
If I correct it will there be a way to make it possible?

I have rectified the excel file

Hi XOR,

Thanks for your reply, the info you gave me works great. I have cleaned up the workbook making sure every name matches.

An issue Im having now is the workbook is slow to respond to typing and entering.
I have looked on the net for similar issues but not had any luck.
Any ideas???

Thanks for your help
Michael

The amount of data the formula from you reads upto 4000 lines. I have very minimal formulas, no formatting , no macros. If you are happy to look at the file I can post it when I'm home.

Appreciate your time
Thanks

Hi XOR,

I have attached the file. There isn't much in there that I would need to enter dummy cells, just a report for work.

Just to recap on what the sheets are about:

copy and paste tab,
I copy data from another workbook, which on average uses about 4000 rows.

Data tab,
This is where you helped me with the formulas to read the previous tab.

Variance tab,
reporting spreadsheet that only uses simple sums and references to the data tab.

Thank you for having a look at it for me.

Michael

Hi,
I have tried the file on 2 pc's with the same result. It happens when its the only workbook open, and also affects another workbook if open at the same time.
I manually enter numbers into the variances tab in the gain and loss columns more than anywhere else, where slowing down is more noticeable, but it seems the whole workbook is affected
in some way. I normally enter a number then enter for the next cell, and sometimes the waiting icon next to cursor appears. The gain and loss columns get deleted of the data entered previously the next day to start a new report, which then selecting cells with data in them is slow.

Hi,

Thanks for your help, Yes it was the same workbook with no changes.
It still does it on my pc, but I found if all the rows of data in the 'data' tab (which is copy and pasted from another workbook) is deleted the workbook isn't slow.
Obviously now its all the data that the formula needs to read is slowing it down??

Thanks
Michael

I normally just copy and paste, I didn't really give 'paste values only' a thought.
The data from the other workbook is filtered, but very possible that it has other formulas, links, formatting etc.
Thanks for the tip, I'll give it a try.


Thanks once again I appreciate your help.
Michael