Archive of Mr Excel Message Board

Back to Forms in Excel VBA archive index
Back to archive home

(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

Check out our Excel Resources

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

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?

problems solved

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

Thanks for the help!

Daniel

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

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<0. Then when the value of A3 is less than zero the conditional formatting you specify will be applied to cell A1.

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

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

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

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

Ada

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.