Summing Numbers With Specific Values To The Left Of Them

**FuzionZero** · 02-12-2007, 05:57 PM

Hi guys im new here and I have a problem that if someone could help me with would make my job alot easier. At my work I am responsible for compiling a monthly report. Well I have to gather data from an Excel document and add up all the totals and then print them out. Well there are like 40+ worksheets and the (lets call them) items arent always in the same locations (along with the values) so i was wondering if anyone knows how i can sum up values from multiple spreads and can do so while still summing up each value that belongs to the same item. Ill attach a screenshot of what one of the worksheets looks like. And remember that not all of these things are in the same order as the other worksheets

**carsto** · 02-12-2007, 09:02 PM

Assuming your column headings are always the same,
try using a Pivot Table with Multiple Ranges
here's how
http://www.contextures.com/xlPivot08.html

If your columns aren't always in the same location, here's some code Antonio (antoka05) wrote for me to delete unnecessary columns and rearrange the remaining columns in a specific order
http://excelforum.com/showthread.php?t=588202

**FuzionZero** · 02-13-2007, 05:09 PM

Im pretty new with Excel (only started using it since I got this job a few months ago) and I am sort of finding this confusing. If someone could take a look at my attached spreadsheet and mabye do it for me (if its not to much work) then tell me how they did it or show me how i can just copy those results. Thanks in advanced. Oh and the final product goes on a new sheet at the end.

EDIT - Just to let you know Plan Type B and two Benefit Code columns are the "items", Card # is not needed, and QTY is how much of the items were made. And as you can see that not all items are in the same spot on all the sheets. Colums A, B, & C are 1 list. D, E & F is another, aswell as G, H and I (3 Lists Side By Side). Thanks!

EDIT - Ignore this attachment. Correct one is a few posts down.

**FuzionZero** · 02-16-2007, 11:07 AM

Im just bumping this topic. it seems that the Excel Forum was down awhile and im sure it was overlooked. So if anyone would mind helping me out on this it would mean alot to me. Thanks.

**starguy** · 02-16-2007, 12:09 PM

Originally Posted by FuzionZero

Im pretty new with Excel (only started using it since I got this job a few months ago) and I am sort of finding this confusing. If someone could take a look at my attached spreadsheet and mabye do it for me (if its not to much work) then tell me how they did it or show me how i can just copy those results. Thanks in advanced. Oh and the final product goes on a new sheet at the end.

EDIT - Just to let you know Plan Type B and two Benefit Code columns are the "items", Card # is not needed, and QTY is how much of the items were made. And as you can see that not all items are in the same spot on all the sheets. Colums A, B, & C are 1 list. D, E & F is another, aswell as G, H and I (3 Lists Side By Side). Thanks!

you can sum by using SUMIF function.
workbook you attached have no data in sheet named Jan06, if you put data in month sheet and attach here then it would be easier to implement the proper function.

**FuzionZero** · 02-16-2007, 02:34 PM

Oh woops haha I feel stupid. I put up the wrong file. Here is the correct file. Sorry haha.

**carsto** · 02-16-2007, 04:13 PM

It's just not apparent what your original data looks like and how you want it is reorganized and totaled.

**VBA Noob** · 02-16-2007, 04:20 PM

Hi,

Do you want to sum all sheets Col C, F & I ??

If so try this

VBA Noob

**FuzionZero** · 02-16-2007, 04:36 PM

Hmm ok I'll show you how i had done it last month. In the zip are 2 files. 1 is where I get the data from and the other is where i had put it all together and totaled it.

In the "printed id cards 2006 totals" document on the "December06" sheet i just put all the data from all the sheets in the "Decemember 2006" document then i used a filter so i can just filter showing only 1 of the items and add up the totals. then in the YTD tab i would put the totals in.

What I am trying to do is get some sort of way to just get all the totals automatically to replace the long process of compiling all the data from the "Month 200X" document and totaling and just have a tab in that document itself that gets all the totals for me.

That way all i have to do is enter the totals it gives me into the YTD Tab of the "printed id cards 200X totals" document.

Hopefully thats a good enough description of what im trying to achieve. Thanks and im sorry for the long post hahaha

It seems the files are too big to host here so i will give a link to them that is hosted elsewhere. Example