Formula to Sum a Column by Fill Colour

[stuartgb100]

Hi,

I have col C containing numbers.
Some cells in col C have a pink fill colour RGB 252:228:214
Some cells in col C have a blue fill colour RGB 221:235:247

In cell H3 I'd like to place a formula that will be the total of all pink cells in col C.
Similarly in J3 a formula for the total of all blue cells in col C.

Can I do this please ?

For better presentation, I'd rather not use two columns (one for each colour).

Thanks.

 

[hiker95]

stuartgb100,

How about something like this?

You will have to color cells H3, and, J3, per the below screenshot.

Excel 2007
	B	C	D	E	F	G	H	I	J	K
1
2		5
3		3					75		45
4		10
5		6
6		15
7		9
8		20
9		12
10		25
11		15
12
13
14
15
Sheet1

Cell Formulas
Range	Formula
H3	=SumByColor(H3,C:C)
J3	=SumByColor(J3,C:C)

How to install the User Defined Function:
1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your Function (given below)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The function is installed and ready to use as shown in the example above.

Function SumByColor(CellColor As Range, SumRange As Range)
' SumByColor Function
' VBA and Macros for Microsoft Excel by Bill Jelen Mr.Excel, Page 84
' Modified 10/30/2016 by hiker95
' =SumByColor(H3,C:C)
' =SumByColor(J3,C:C)
' =SumByColor(cell_address_where_a_specific_color_is, range_to_Sum)
Dim myCell As Range
Dim iCol As Integer
Dim myTotal
iCol = CellColor.Interior.ColorIndex
For Each myCell In SumRange
  If myCell.Interior.ColorIndex = iCol Then
    myTotal = myTotal + myCell.Value
  End If
Next myCell
SumByColor = myTotal
End Function

 

[stuartgb100]

Many thanks for the help.

I can see how that works in vba, and it is certainly a more elegant solution than my effort would have been !

The friend I'm doing this for, tells me that macro-enabled workbooks are forbidden on his company's system.

Apologies for not specifically saying I was after a non-vba solution.
However, I did ask for a formula.

Is a formula possible, or should I find another solutions - perhaps using two columns (one for each colour) ?

Thanks again.

 

[AliGW]

To do that, we will need to know the criteria for pink and blue cells - what conditions do they fulfil? How have you set up their conditional formatting?

 

[hiker95]

Many thanks for the help.

I can see how that works in vba, and it is certainly a more elegant solution than my effort would have been !

The friend I'm doing this for, tells me that macro-enabled workbooks are forbidden on his company's system.

Apologies for not specifically saying I was after a non-vba solution.
However, I did ask for a formula.

Is a formula possible, or should I find another solutions - perhaps using two columns (one for each colour) ?

Thanks again.

stuartgb100,

You are welcome.


It looks like AliGW may have a solution for you.

 

[stuartgb100]

Ali,

I have no conditional formatting.

I am trying to set up a simple spreadsheet to keep track of a Company's overheads.

Here is what i have so far:

Col A has the Overhead Elements with the values 'Staff Salaries' in A8 and 'Employee Wages' in A10.
E6 to P6 has values for months of the year (eg. Jan, Feb etc.).
E8 to P8 have a pink fill colour.
E9 to P9 have a blue fill colour.
E10 to P10 have a pink fill colour.
E11 to P11 have a blue fill colour.

There are more overhead elements down col A, and each has a pink and blue row to accompany the element.

My idea is that the Company can enter target expenditure into the red cells for every projected element, and as time goes by enter actual figures into the blue cells.

I have C8 holding the total of E8:P8 (the projected total) PINK
and C9 holding the total of E9:P9 (the actual total) BLUE
This follows down col C.

So col C holds the element totals for the year, both projected and actual, and where the cells have a pink or blue fill colour (as appropriate).

I have designated H3 to take the total of the projected "pink" values in col C, and J3 to take the actual "blue" values.

So that is where my question originates - how to separately total the pink and blue cells/values in Col c>

Regards and thanks.

 

[AliGW]

OK - so it's date related, yes? In that case, is there a column with dates or months in it? Otherwise, you are going to need to be able to tag the data in some way other than a colour fill. It would be useful to provide some sample data so we can see the layout.

 

[stuartgb100]

How do I upload a file, please ?

 

[My Aswer Is This]

If you want a Vba script.
Try this:

Sub Sum_My_Colors()
Dim c As Range
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row
Dim Pink As Long
Dim Blue As Long
Pink = 0
Blue = 0
    For Each c In Range("C1:C" & Lastrow)
        If c.Interior.Color = RGB(252, 228, 214) Then Pink = Pink + 1
        If c.Interior.Color = RGB(221, 235, 247) Then Blue = Blue + 1
    Next
    Range("H3").Value = Pink
    Range("J3").Value = Blue
    
End Sub

 

[AliGW]

The OP has specifically stated that a VBA solution is not possible here.