Sum totals across different columns

jaingalang

New Member
I have a spreadsheet with four years of quarterly Income Statement data for 3 different companies (48 columns across, starting from column B). Each company is grouped together (i.e. the first 16 columns is data for company X, the next 16 are for company Y, etc.). Down column A is a list of income statement accounts (i.e Revenues, Expenses, Net Income, etc.)

I'd like a formula that allows me to show the consolidated (sum) amounts for each entity across each account (i.e Total Revenues for company X, Y & Z = \$X.XX MM, or total Net Income for Company X,Y,Z). I'd need to show this data for the 16 quarters.

Since the entities are across various columns, how can I ensure that the correct accounts are being summed across the correct periods using correct entities?

I get that I could possible go =SUM(Q1 Net Income Company X + Q1 Net Income Company Y + Q1 net Income Company Z) manually, but was hoping for a formula. Sumifs? Vlookup?

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

FDibbins

Well-known Member
Take a look at using the SUMIF() function....
 A​ B​ C​ D​ E​ F​ G​ H​ I​ J​ 1​ a1 a2 a3 b1 b2 b3 c1 c2 c3 2​ aa 1​ 2​ 3​ 4​ 5​ 6​ 7​ 8​ 9​ 3​ bb 2​ 4​ 6​ 8​ 10​ 12​ 14​ 16​ 18​ 4​ cc 4​ 8​ 12​ 16​ 20​ 24​ 28​ 32​ 36​ 5​ all 2's 6​ 15​
a6=SUMIF(\$B\$1:\$J\$1,"*2",B2:J2)

yOU COULD OBVIOUSLY REPLACE "*2" WITH A SPECIFIC WORD

jaingalang

New Member
Thanks Ford, but what does 'All 2's' mean?

Replies
0
Views
213
Replies
5
Views
393
Replies
0
Views
287
Replies
1
Views
153
Replies
8
Views
534

1,195,961
Messages
6,012,573
Members
441,712
Latest member
MartinGM

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.

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

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