# Sum Data in Multiple Columns

#### hblais

##### New Member
I have an excel sheet with about 250 columns worth of data (A:IQ). Column A contains a unique value/header (9025 rows), and then each column (B:IQ) contains data associated with/for that value. This data is a combination of a year with a dollar amount for that year in the immediate column to the right. A single value from column A may have the same year in multiple columns, with different corresponding dollar amounts (a single column does not contain the same year all the way down).

I would like to perform a calculation that adds up the dollar value associated each year in the column to the left of the dollar value, so that for each row, I have a single dollar amount for each year. Since I am reading across the row, I know I can’t use a sumif function. At this point, I am at a loss of how to get this calculation. Sample data and the ideal results are below. Any thoughts/guidance are sincerely appreciated.

Example:
Source File
Title (A) Year (B) Amount (C) Year (D) Amount Year(F) Amount(G)
NAME 1 2011 100 2011 125 2013 50
NAME 2 2011 50 2012 50 2012 100
NAME 3 2012 100 2013 25 2013 100

Desired Result:
Title(A) 2011(B) 2012(C) 2013(D) 2014(E) 2015(F)
NAME 1 225 0 0 0 0
NAME 2 50 150 0 0 0
NAME 3 0 100 125 0 0

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### Weazel

##### Well-known Member
maybe something like...

Excel 2013
 A B C D E F G 1 Title Year Amount Year Amount Year Amount 2 NAME1 2011 100 2011 125 2013 50 3 NAME2 2011 50 2012 50 2012 100 4 NAME3 2012 100 2013 25 2013 10 5 6 2011 2012 2013 2014 2015 7 Name1 225 0 50 0 0 8 Name2 50 150 0 0 0 9 Name3 0 100 35 0 0

<tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
B7{=SUM(IF(\$A\$2:\$A\$4=\$A7,IF(\$B\$2:\$F\$4=B\$6,\$C\$2:\$G\$4)))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Replies
0
Views
139
Replies
12
Views
422
Replies
1
Views
163
Replies
1
Views
484
Replies
4
Views
172

1,191,167
Messages
5,985,053
Members
439,936
Latest member
BSR

### 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?

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