Sum Data in Multiple Columns

[hblais]

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

 

[Weazel]

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 Cell Formula 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>