Results 1 to 5 of 5

Thread: Excel 2007: Can it SUMIF( ) using color as a condition ?

  1. #1
    Join Date
    Nov 2011
    Posts
    94

    Excel 2007: Can it SUMIF( ) using color as a condition ?

    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?

  2. #2
    Join Date
    Jun 2011
    Posts
    635

    Re: Excel 2007: Can it SUMIF( ) using color as a condition ?

    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

  3. #3
    Join Date
    Jun 2011
    Posts
    487

    Re: Excel 2007: Can it SUMIF( ) using color as a condition ?

    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

  4. #4
    Join Date
    Jun 2011
    Posts
    798

    Re: Excel 2007: Can it SUMIF( ) using color as a condition ?

    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

  5. #5
    Join Date
    May 2011
    Posts
    448

    Re: Excel 2007: Can it SUMIF( ) using color as a condition ?

    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.

Similar Threads

  1. Replies: 2
    Last Post: 21-02-2012, 04:02 PM
  2. Replies: 6
    Last Post: 17-05-2011, 10:00 PM
  3. Adding column of numbers with condition in Excel
    By Abbiey in forum Windows Software
    Replies: 4
    Last Post: 08-01-2010, 10:19 PM
  4. How do i change font color in a range on Excel 2007
    By AK_Chopra in forum Windows Software
    Replies: 3
    Last Post: 09-06-2009, 08:16 PM
  5. Replies: 4
    Last Post: 23-12-2008, 06:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,750,806,676.68653 seconds with 16 queries