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 |
---|
|
---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T |
---|
1 | | | | | | | | | | | | | | | | | | | | Sum of White |
---|
2 | | 10 | 20 | 30 | 40 | 50 | 10 | 20 | 30 | 40 | 50 | 10 | 20 | 30 | 40 | 50 | 10 | | | 100 |
---|
3 | | | | | w | | w | | | | w | | | | | | | | w | |
---|
4 | | | | | h | | h | | | | h | | | | | | | | h | |
---|
5 | | | | | i | | i | | | | i | | | | | | | | i | |
---|
6 | | | | | t | | t | | | | t | | | | | | | | t | |
---|
7 | | | | | e | | e | | | | e | | | | | | | | e | |
---|
8 | | | | | | | | | | | | | | | | | | | | |
---|
|
---|
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