Forum Discussion
Solved
Sum cells if data is present in other cells
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...
In CY2:
=INDEX(B2:CS2,MATCH(9.99999999999999E+307,B2:S2))
or
=LOOKUP(9.99999999999999E+307,B2:CS2)
In CX2:
=SUM(B2:CS2)-CY2
In CY2:
=INDEX(B2:CS2,MATCH(9.99999999999999E+307,B2:S2))
or
=LOOKUP(9.99999999999999E+307,B2:CS2)
In CX2:
=SUM(B2:CS2)-CY2
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
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