Need help w/ Formula please!!!

Hi-

Could someone please help-

I am doing a finacial spread sheet. Looking for a formula that allows me to add in new columns without having to go back and change the formula, by adding additional columns.

I started out with chart numbers that vary like 5000, 5002, 5530, 5532. These are the only numbers I am using. Now what I would like is to find a formula that works with finding the amount each chart number adds up to. (so if I were to add in another column with a matching chart number I would get the sum and not have to change anything.


Ex: (each in its own column)

Chart #: 1 2 3 4 2 3 3 4 2 4 3 3 3 3 4 3 3 3

and under the chart #'s are the amounts that match up to each additional chart number. some are left blank.

Lets say chart# 1=$0 and chart 2= - 1 and chart 3 varies in amounts, so I would need the sum of each chart #.

Chart# 1 ---- need formula that Matches the chart # to give me a SUM for each and works with adding in additional columns by using the same chart #'s-
Chart# 2
Chart# 3
Chart# 4

Would like to add additional columns later without having to change the formula. Does anyone know how I go about doing this?????? If anyone could please help. I greatly appreciate it! Thanks = )

Assuming the following...

1) first row contains your chart numbers, starting at B1

2) second row contains your values to be summed, starting at B2

3) Column A contains your list of chart numbers, starting at A5

4) chart numbers in the first row and in Column A are both formatted as 'General', not 'Text'

Formula...

B5, copied down:

=SUMIF($B$1:INDEX($B$1:$IV$1,MATCH(9.99999999999999E+307,$B$1:$IV$1)),A5,$B$2:INDEX($B$2:$IV$2,MATCH(9.99999999999999E+307,$B$1:$IV$1)))

Your totals will automatically include any new amounts entered.

Hope this helps!

Originally Posted by nEW2eXCEL

Domenic
I attached an example... Thank you for trying to help! = )