I understand that Excel 2007 can sort by colors. It is a great feature indeed. May I know if =SUMIF( ) could use a color as the condition to sum up color shaded cells or cells with certain font color?
I understand that Excel 2007 can sort by colors. It is a great feature indeed. May I know if =SUMIF( ) could use a color as the condition to sum up color shaded cells or cells with certain font color?
There is no built-in excel function to do this. But you can use a UDF that looks at the range and returns the sum of color. But that function will not recalculate if you change color. Every time you change the color you will need to recalculate or wait excel to recalculate. To install the UDF (User Defined function). From workbook launch VBE using
Alt+F11. From menu Insert a Module and paste the below function.Close and get back to workbook and try the below formula.
Code:A1:A10 is the range to be checked. B1 is a lookup cell coloured .. =colorsum(A1:A10,B1) =colorcount(A1:A10,B1) 'function to sum values within colorred cells Function ColorSum(varRange As Range, varColor As Range) As Variant Dim varTemp As Variant, cell As Range ColorSum = 0 For Each cell In varRange If cell.Interior.ColorIndex = varColor.Interior.ColorIndex Then If IsNumeric(cell.Value) Then ColorSum = ColorSum + cell.Value End If Next End Function 'Function to count the colored cells (not colored through Conditional formatting) Function ColorCount(varRange As Range, varColor As Range) As Variant Dim varTemp As Variant, cell As Range ColorCount = 0 For Each cell In varRange If cell.Interior.ColorIndex = varColor.Interior.ColorIndex Then ColorCount = ColorCount + 1 End If Next End Function
You cannot do a native sumif by colors. You must create a VBA function for that. Take a look here,
Code:Function SumCuloare(Culoare As Range, Casute As Range) 'Definirea variabilelor Dim rrRange As Range Dim sumColor As Long Dim rrCasute As Range 'Definirea constantelor sumColor = 0 Set rrCasute = Casute vCuloare = Culoare.Font.Color ' Suma pe culori For Each rrRange In rrCasute If rrRange.Font.Color = vCuloare Then sumColor = sumColor + rrRange.Cells.Value End If Next rrRange ' Returnare rezultat SumCuloare = sumColor End Function
Directly is probably hardly walk, because if the counting-function refers to a value of the cell contents and not on formatting the cell. However, since the conditional formatting yes itself contains an if-function, I wonder whether it might be easier to use them directly for counting the if-function.You have obviously come loop with its reference already enough information for the solution. I have exactly the same need as you: I would like to include in a table, all with a certain color highlighted cells. Could you give me the solution as a formula.Opportunities are already available. But this is very elaborately. You'd have to program it all by hand in VBA. In addition, you will not fix it, that it runs automatically. At least not in reasonable time
Built into Excel, git no way that provides for this. In the new version 2007 you can either by car filters filter colors and so on then perform with SUBTOTAL () conditional. It gives a bit opportunities with an old XL4 macro to read the Fabe an adjacent cell and then to provide the basis of which a calculation. The third option is to write a custom function in VBA, which then allows these calculations - the need to be a little VBA and programming skills. With all but continue to apply the following restrictions. They are first assigned the color of the Conditional Formatting can not be read - because you have to rely on the formula and a pure change the formatting of a cell, the calculation in the cells, which refer to the color does not automatically trigger new.
Bookmarks