Sum data in various columns in one sheet in another sheet based on criteria

ciecie66

New Member
Joined
Sep 23, 2012
Messages
5
I have a problem I am trying to solve with a spreadsheet that is attached to a DB. I have a spreadsheet that pulls in monthly data for my actual and forecast category by company code in the columns and accounts in the rows. Every month the number of columns or rows could change depending on what has been posted to the general ledger. I need to get the data that is in the data sheet by company code by month (multiple columns) summed into 1 column per company code on another sheet in the workbook. I have a sample workbook that shows what I am trying to do. I tried using sumif but it's returning all zero's when I know that is not correct. If I can't do it with formulas I would like to know how to do it in VBA. I am very new with VBA but want to learn it if possible. I don't know how to attach the workbook with my examples in it to this message so I will try to explain it here.

datasheet looks like this: Columns
time Jan.2012 Jan.2012 Jan. 2012 Feb. 2012 Feb. 2012 Feb. 2012
company 1001 1002 1003 1001 1002 1003
category Actual Actual Actual Forecast Forecast Forecast
Accounts in rows:
Revenue 1 700 500 500 600 300 400
Revenue 2 200 100 150 160 170 180
Revenue 3 100 800 700 100 140 120



Final sheet looks like this: Columns

company 1001 1002 1003

Accounts in rows:
Revenue 1 1600 800 900
Revenue 2 360 260 330
Revenue 3 200 900 820
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
Can you place the sheet on DropBox.com and give us the linkl
 

Watch MrExcel Video

Forum statistics

Threads
1,133,525
Messages
5,659,327
Members
418,497
Latest member
VAllen79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top