Sum function not picking up one cell's value

Question

I have a column filled with values, most of which are the results of formulas. When trying to sum the entire column, or even the specific cells which have values, one of them is missed. It has the following formula calculating the value

=IFS(G5<1,"0",G5<21,"3",G5<41,"5",G5<61,"7",G5<81,"9",G5<101,"11")

When I change any of the figures that contribute to the value in the cell not being included in the sum equation, the overall total still does not change.

I have also tried another formula, very basic that does only add the problem cell and one other. This also does not work. I have tried reformatting to number, checking it is in automatic, trying different formulas but no success.

I am thinking it is something particular to this cell??

Answer 1

Hi Gary,

I have a column filled with values, most of which are the results of formulas. When trying to sum the entire column, or even the specific cells which have values, one of them is missed. It has the following formula calculating the value

=IFS(G5<1,"0",G5<21,"3",G5<41,"5",G5<61,"7",G5<81,"9",G5<101,"11")

When I change any of the figures that contribute to the value in the cell not being included in the sum equation, the overall total still does not change.

I have also tried another formula, very basic that does only add the problem cell and one other. This also does not work. I have tried reformatting to number, checking it is in automatic, trying different formulas but no success.

I am thinking it is something particular to this cell??

Replace your formula with:

=IFS(G5<1,0,G5<21,3,G5<41,5,G5<61,7,G5<81,9,G5<101,11)

Adding the quotes around each numeric value converts the result to the corresponding text value and, as such, will be ignored in the formula summation.

===

Regards,

Norman

Answer 2

Norman, your a champ.

Much appreciation.