(1.) Sum values only (ignore text and #value!); (2.) formatting cell based on another cell value

Posted by Daniel P. on July 14, 2000 6:16 AM

Two questions:

(1.) I need to total the values within a column but can't due to all the text and #value! data. Right now I'm sorting them out but it's a pain. Any ideas?

(2.) Can I format a cell (either change text color or background color) based on another cells value? Basic example...

Data entered into A1=5
Data entered into A2=6
Formula entered into A3=A1-A2 (value would be -1)

The condition I need is if A3 is negative, the cell or text in A1 will be reformatted to throw up a flag. (red text or something)

Thanks for all help you guys provide!
Daniel

Posted by elizabeth on July 14, 0100 9:02 AM

The text should not be a problem in your sums. If the #values are #div/0, you can set up a condition that if the divisor is 0 the cell value be 0. what other #values do you have?

Posted by Daniel P. on July 17, 0100 11:00 AM

problems solved

Thanks for the help!

Daniel

Posted by jrbee on July 14, 0100 11:46 AM

1.) Try using an additional column with a formula like =if(isnumber(c1),c1,"") in each row. This will generate a column that contains a copy of the column c value only if it is a number. Then use a regular sum function at the bottom of this new column and your all set.

You could also try using the SUMIF function on your original column of data. Something like "=SUMIF(c1:c10,">-10000")". This will sum all the values that are greater than -10000 (which will eliminate the text and error messages). Use whatever criteria is appropriate for your data.

2.) The conditional formatting option has what you want. In your example, select cell A1 and select Format - Conditional Formatting. Select the "Formula is" option and in the formula field enter =A3&LT;0. Then when the value of A3 is less than zero the conditional formatting you specify will be applied to cell A1.

Posted by Ada on July 14, 0100 10:07 PM

Re: (1.) Sum values only (ignore text and #value!)

Daniel
Re your first question.
One way is with an array formula(Ctrl+Shift+Enter) :-

=SUM(IF(ISERROR(A1:A10),"",A1:A10))

Ada