Sum cells if data is present in other cells

[robertc3]

I am creating a sheet that has 96 columns (for months of a project) and I need to sum certain cells if there are values in the columns. Each month an amount will be typed into one of the cells in one of the 96 columns, for example, B2 and then the next month a new amount typed into C2, then D2 and so on.

The way this needs to work is that if there is data in say B2,C2,D2,E2 I need cell CX2 to sum B2,C2,&D2 and then cell CY2 to display the value of cell E2.  The following month there would be data is B2,C2,D2,E2,F2  and I would need cell CX2 to sum B2:E2 and cell CY2 to display F2. Is this possible in excel?

Sorry if something like this has already been asked. I couldn't figure out how to craft a search that returned meaningful results.

[#2 · Accepted solution]

@robertc3 

In CY2:

=INDEX(B2:CS2,MATCH(9.99999999999999E+307,B2:S2))

or

=LOOKUP(9.99999999999999E+307,B2:CS2)

In CX2:

=SUM(B2:CS2)-CY2

[#3]

Thank you very much! This is perfect.

[#4]

Hans,
One more question if I may. The cells that the =INDEX or =LOOKUP formulas apply to are looking at cells that have a formula in some cases. These formulas (SUM of cells) report a $0.00 value when there is nothing in the cells they are SUMing. Is there a way to have the =INDEX or =LOOKUP ignore a zero value? I can hide the zero values, but they are still throwing off the INDEX and LOOKUP.
Thank you,
Robert

[#5]

@robertc3 

Use

=INDEX(B2:S2,MATCH(1,INDEX(1/(B2:S2<>0),,)))