Performing calculations with cells with same fill color and containing letters to which have been assigned numerical values. I need a super MS Excel 2

[dellemara]

I work in a school and need urgently a formula which would allow me to sum the content of cells with same fill colour, containing each, one letter, to which has been assigned a numerical value.

I can calculate separately:

1. The total of any range of cells containing letters with given numerical value using the SUMPRODUCT function as shown in the example below:

=SUMPRODUCT(COUNTIF(F2:AI2,{"A*","A","B","C","D","E"}),{140,120,100,80,60,40})

This formula works perfectly but does not give the chance to perform the calculation only on cells with same fill colour.

2. The total of any range of cells with same fill colour, containing numbers, thanks to a macro which enables a user defined function as shown in the example below:

Function SumByColor(InputRange As Range, ColorRange As Range) As Double
' returns the sum of each cell in the range InputRange that has the same
' background color as the cell in ColorRange
' example: =SumByColor($A$1:$A$20,B1)
' range A1:A20 is the range you want to sum
' range B1 is a cell with the background color you want to sum
Dim cl As Range, TempSum As Double, ColorIndex As Integer
' Application.Volatile ' this is optional
ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
TempSum = 0
On Error Resume Next ' ignore cells without values
For Each cl In InputRange.Cells
If cl.Interior.ColorIndex = ColorIndex Then
TempSum = TempSum + cl.Value
End If
Next cl
On Error GoTo 0
Set cl = Nothing
SumByColor = TempSum
End Function


The formula based on this function called SumByColor works perfectly well, see example on the use of this formula below:

=SumByColor(AC131:AC135,AC131)

The formula calculates the sum of all the cells within the range AC131:AC135 with same fill color as in the sample cell AC131.

How do I use the two functions together maybe with a nested formula, (if that is at all possible) or what formula would allow me to perform the sum as described at the beginning of this message?

I would be extremely grateful if anybody could solve this mistery for me as I do not have this level of expertise in Excel. Many thanks.

 

[patel45]

attach a link to a sample file, would be helpful for not super expert people

 

[Andrew Poulsom]

Actually, I think you will find that your formula doesn't work because the asterisk in A* is treated as a wildcard. So A will be counted twice.

Maybe you can use Chip Pearson's ColorIndexOfRange function somehow:

Color Functions In Excel

 

[dellemara]

Dear Mr Poulsom, you were absolutely right as the A* (which represents a mark and has been assigned a numerical value of 140) is not treated as a string as I thought it would, because of the speech marks. I could still use this formula even if it doesn't achieve entirely my object as described in my original message. How would I get around the *, wild card problem in this case?

Also thank your for your link which will be extremely useful for few different applications I have in mind. The problem I am left with, at the moment, is that I can calculate the sum of the content of a range of cells with same fill color, only if all the cells in that range contain numbers but not if they contain, each, a letter to which have been assigned a numerical value.

Sorry about the convoluted explanation, the problem is that I am Italian and English is therefore not my first language. I would really appreciate some further help on this matter. Many thanks.

 

[dellemara]

Hi thank you for your message; here is the link to a sample file which I have prepared to show exactly what I need to achieve http://sdrv.ms/RtXnTS . Your help would be very much appreciated. Many thanks.

 

[Andrew Poulsom]

You can overcoming the double counting by preceding the asterisk with a tilde (the escape character):

=SUMPRODUCT(COUNTIF(F2:AI2,{"A~*","A","B","C","D","E"}),{140,120,100,80,60,40})

 

[dellemara]

Thank you, patel45, for showing interest in my problem, the link you requested is http://sdrv.ms/RtXnTS I do hope you will be able to help! Many thanks.

 

[dellemara]

Many thanks to Mr Poulsom, the "SUMPRODUCT" formula works perfectly well.

 

[Jerry Sullivan]

Here's a custom worksheet function that you could try.

Function SumColorLookups(rMatchColor As Range, rSumRange As Range, _
    rLookup As Range) As Variant

    
    Dim c As Range
    Dim lColor As Long
    Dim dSumTotal As Double, dVal As Double

    
    Application.Volatile True

    
    lColor = rMatchColor.Interior.Color

    
    On Error Resume Next
    For Each c In rSumRange
        If c.Text <> "" Then
            If c.Interior.Color = lColor Then
                dVal = Application.VLookup(Replace(c.Text, "*", "~*"), rLookup, 2, 0)
                If Err.Number = 0 Then
                    dSumTotal = dSumTotal + dVal
                Else
                    Err.Clear
                End If
            End If
        End If
    Next c
    On Error GoTo 0
    SumColorLookups = dSumTotal

End Function

It assumes that you set up a lookup table (you could do this in place of your existing legend or in a hidden range).

Excel Workbook
	B	C
20	Points Awarded
21	A*	140
22	A	120
23	B	100
24	C	80
25	D	60
26	E	40
Sheet

It also assumes you reference a cells that has the matching color. In this example, it shows those above your totals headers.

Excel Workbook
	AI	AJ
3
4	Total (only values in cells with white fill color)	Total (only values in cells with red fill color)
5	480	120
6	260	80
7	160	60
8	380	0
9	320	60
Sheet

 

[dellemara]

Many, many, many thanks, the macro and formulas you provided in your messages work perfectly well. I am so grateful for your time and efforts.