Counting and adding values int two different columns

[migpics]

I'm developing a basic Life Cycle Cost Analysis Spreadsheet and need some
assistance adding the costs of particular activity into a specified year.
Example:

This column checks

if the Cash Flow Year

is listed in the "Year

Cost Occurs" and

and then adds it to the

other rows that apply

for a total cash flow

year.
Cost$ Year Cost Occurs | Cash Flow Year Total in
Cash Flow Year
300 0,2,4,5 | 0
=330+225
225 0,1,3,5 | 1
=225+125
125 1,2,3,4,5 | 2
=125+300
3
=225+125
4
=300+125
5
=300+225+125

I am hoping someone can have some code that will look at the Year cost
occurs and then add it to the Total in Cash Flow Year.

Currently, this is the code I use
=IF(ISNUMBER(SEARCH(","&A2&",",","&'Alternative 1'!$F$8&",")),'Alternative
1'!$G$8, 0)+IF(ISNUMBER(SEARCH(","&A2&",",","&'Alternative
1'!$F$9&",")),'Alternative 1'!$G$9,
0)+IF(ISNUMBER(SEARCH(","&A2&",",","&'Alternative 1'!$F$10&",")),'Alternative
1'!$G$10, 0)

It applies to two different sheets but the lenght of the string is limited
so my formula is basically too long.
Any suggestions to create a loop through an array function?
Thanks
Miguel

 

[Joel]

You don't have to look for commas in your formula. Since you are looking for
only the numbers 1 to 5 just look for these numbers and nothing else. You
could replace Search with FIND which will also reduce the number of
characters.

=IF(ISNUMBER(SEARCH(A2,'Alternative 1'!$F$8)),'Alternative
1'!$G$8)+IF(ISNUMBER(SEARCH(A2,'Alternative 1'!$F$9)),'Alternative
1'!$G$9,0)+IF(ISNUMBER(SEARCH(A2,'Alternative 1'!$F$10)),'Alternative
1'!$G$10)

 

[migpics]

Thanks Joel. The final version will go all the way to 100 years so I need to
make sure there is some way it doesn't count all the one's for example if the
number 1 is in the number 10 it would be thrown off.
Thanks!
Miguel

 

[dan dungan]

Why not set it up in your spreadsheet this way?

Your set up seems so complex. And adding 100 years seems like it
will be too many characters for your formula.

A B C D E F

Year Cost Cost 2 Cost 3 Total
0 2000 225 300 525 =sum(C1:E1)
1 2001 125 225 350
2 2002 125 300 425
3 2003 125 225 350
4 2004 125 300 425
5 2005 125 225 300 650
6 2006
7 2007
8 2008
9 2009
10 2010
11 2011
12 2012
13 2013
14 2014
15 2015
16 2016


Dan

 

[migpics]

Thanks Dan,
The reason I don't want to set it up that way is because I want people just
to be able to input event dates on a separate sheet and the cash flow diagram
to be updated.
Thanks!
Miguel