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" wrote:

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