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
=======
