MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sum only formatted cells...


Posted by Rino on September 25, 2001 11:14 AM

hi,

i have a string of cells. some are red in color and some are blue. what function do i use to sum on the cells in red?

ex:
numbers 2,3,4 are formatted red
numbers 1,9 are formatted blue

i would like my formula to return the sum of only the red cells


Posted by Aladin Akyurek on September 25, 2001 11:23 AM

Rino,

You can use the very same condition that you use to color a cell red in a formula which must sum them. What condition do you use to color a cell red?

Aladin

==========

Posted by rino on September 25, 2001 12:30 PM

it wasn't the cell that i colored red, it was font. so, to review, i want to sum cells of a certain Font Color

thanks


Posted by Aladin Akyurek on September 25, 2001 12:56 PM

> it wasn't the cell that i colored red, it was font. so, to review, i want to sum cells of a certain Font Color

Rino --

That doesn't make any difference.

Suppose I have the following numbers in A1:A5.

3 (font color = red)
8
2 (font color = red)
1 (font color = red)
6

My rule/condition to color the font is: if cell-value < 5, make it red.

Now I'm interested in summing the cells with font color red. I'd use

=SUMIF(A1:A5,"<5")

Suppose my rule for coloring is a bit more complicated: if cell-value is greater than or equal to 1 and less than 5, make it red. Again, I'm interested in summing those cells which I colored red. I'd use

=SUMPRODUCT((A1:A5>=1)*(A1:A5 < 5)*(A1:A5))

The above is what I was trying to tell.

Aladin

Posted by rino on September 25, 2001 1:15 PM

so i would have to reference the conditional formatting value.

i can's just say "in plain english" sum all cells in where font color = red. is there a syntax when using the sumif function?
thanks

Posted by rino on September 25, 2001 1:15 PM

so i would have to reference the conditional formatting value.

i can's just say "in plain english" sum all cells in where font color = red. is there a syntax when using the sumif function?
thanks

Posted by Aladin Akyurek on September 25, 2001 1:22 PM

Yep. What did you use there?

No.

is there a syntax when using the sumif function?

Yep. See my example. For a fuller description, see Help|Contents and Index (Excel 2000).

Aladin

=======

Posted by Aladin Akyurek on September 25, 2001 1:22 PM

Yep. What did you use there?

No.

is there a syntax when using the sumif function?

Yep. See my example. For a fuller description, see Help|Contents and Index (Excel 2000).

Aladin

=======