SUM Content of a set of cells with a certain color

ALProject

New Member
Joined
Jan 31, 2014
Messages
6
I have a set of cells i want to sum. within them I have a few cells I do not want counted. How could i do this.

I want to SUM the contents of B2:Q2 but not count the contents of the cells what are not white in this case.

Thanks in advance.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
ALProject,

I want to SUM the contents of B2:Q2 but not count the contents of the cells what are not white in this case.

1. You want to sum/count the cells in the range that ARE White?

2. Or, you want to sum/count the cells in the range that ARE NOT White?

What is correct, 1 or 2?
 
Upvote 0
I want to SUM the contents of all white cells. If i have 5 cells, 2 of which are colored green, I only want the SUM of the three white cells. Not how many cells there are but the SUM of the contents. If that makes sense.
 
Upvote 0
ALProject,

By the way, you never mentioned what version of Excel and Windows you are using. The below Function will work in Excel 2007 and newer.

SUM the contents of B2:Q2

I am using your original range in the below screenshot.


Sample worksheet (cells E2, G2, K2, and, S2 are white - you may not be able to see the white color):


Excel 2007
ABCDEFGHIJKLMNOPQRST
1Sum of White
210203040501020304050102030405010100
3wwww
4hhhh
5iiii
6tttt
7eeee
8
Sheet1
Cell Formulas
RangeFormula
T2=ColorFunction($S$2,$B$2:$Q$2,TRUE)



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below Function
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel

Code:
Option Explicit
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
' http://www.brainbell.com/tutorials/ms-office/excel/Count_Or_Sum_Cells_That_Have_A_Specified_Fill_Color.htm
'
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
  For Each rCell In rRange
    If rCell.Interior.ColorIndex = lCol Then
      vResult = WorksheetFunction.SUM(rCell) + vResult
    End If
   Next rCell
Else
  For Each rCell In rRange
    If rCell.Interior.ColorIndex = lCol Then
      vResult = 1 + vResult
    End If
  Next rCell
End If
ColorFunction = vResult
End Function

Before you use the Function with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then add the Function formula in cell T1:
=ColorFunction($S$2,$B$2:$Q$2,TRUE)


Here is a link to where the function came from:
Ms-office - Excel - Count Or Sum Cells That Have A Specified Fill Color Tutorials
 
Last edited:
Upvote 0
ALProject,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top