Free Microsoft Excel 2013 Quick Reference

"Sum only the colored cells" custom function

I am trying to sum only the colored cells in a column range, and the colors
are applied with conditional formatting. Someone recommended the above page
to me, and it looks like exactly what I am looking for - I just can't get the
one function I'm after to work.

Onthis page, the IndexColorOfCF, ColorOfCF, and
CountOfCF all work for me. The SumByCFColorIndex does not return an error,
but it is always evaluating to zero. I am putting the range, then an integer
as the function arguments.

I tried 0,1,2,-1, and others to try and see if it would sum the values of
the colored cells in the range I specified. I tried to look at the code, but
I don't know what integer it is looking for for it to work.

It is the last function on this page. If this would work, I'm all set! If
someone who understands this code could tell me, that would be great.

Thanks for any help you can offer.

Post your answer or comment

comments powered by Disqus
I want to accomplish the following result. example. during each month i have to open up jobs for our service company. some jobs are inspections. my boss wants to keep a running dollar amount total for inspections for each month. I would like to either enter the dollar amount in the cell then change the fill color or change the font color in every inspection cell. i then want to sum only the colored cells or the cells that have the selected font color.

Excel Subotal Function. Sum, Count etc only the visible cells after ...

Excel Subotal Function. Sum, Count, Average etc only the visible cells after filtering. ... Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ...

I have a worksheet where I add every week a column with numbers. I insert a
new column for the new week and the next cell has a formula that sums only
the last four cells.

The thing is that when I insert the column, the sums stays refering to the
previous 4 and does not consider the new one. I need this sum to consider
ONLY the last 4 coulmns to te left of that, any ideas? Can you refer
dynamicaly to the previous 4 cells in order to sum them??

Thanks in advance,
Rds, from Argentina

How do I reference a cell that has a colored background? I would like to sum
only the colored cells in a row. The cells are colored manually to identify

Suppose I filled cells from a1 to a100 with numbers from 1 to100 except a8 which still empty and ,also, other cells
So, I want to select the column (a) then copy and paste only those filling cells except the empty cells.
I thought I can use "paste special" and choose "skip blank" to get only the filling cells, but I failed!
Can you help me!

I would like to SORT a column and then SUM only the SORTED entries. For example, lets say I have two columns: Cost and Type. Cost will be dollar or Euro amounts, while type will be "rent", "utilities", etc. And at the bottom is the SUM.

When I do a data SORT just to see "utilities", for example, I would like the sum to change to reflect only the utilties. Better yet, I would like to be able to look up only those entries next to the word "utilities" and get the sum of those only (regardless of what I am viewing by SORT).

Or whatever the best recommendation is out there.

Thank you. (Excel 97)

How can I protect only the highlighted cells in Excel?

Please I need your help.
How to locate and calculate ONLY the empty cells after append new data?
Thank you in advance.

' Locate the FinalRow
FinalRow = Cells(Rows.Count, 2).End(xlUp).row
' Cost Net
Range("K3:K" & FinalRow).Formula = "=IF(J3<>" & Chr(34) & Chr(34) &
",ROUND(J3*(1-($O$2%)),2)," & Chr(34) & Chr(34) & ")"
'   Unit Price
Range("I3:I" & FinalRow).Formula = "=IFERROR(ROUND(K3*(1+$N$2%),2), " & Chr(34) & Chr(34)
& ")"

'Range("I3:I" & FinalRow).Formula = "=IF(H3<>" & Chr(34) & Chr(34) &
",ROUND(K3*(1+($N$2%)),2)," & Chr(34) & Chr(34) & ")"
' Total Cost
Range("L3:L" & FinalRow).Formula = "=IF(K3<>" & Chr(34) & Chr(34) &
",H3*K3," & Chr(34) & Chr(34) & ")"
' Total Sales
Range("M3:M" & FinalRow).Formula = "=IF(I3<>" & Chr(34) & Chr(34) &
",H3*I3," & Chr(34) & Chr(34) & ")"

I don't know if this is possible, but would greatly help me out if it is.
I want to know if it's possible to add the sums of certain color cells only.
For example, when I color code my sums green, that means I have it in my
account. Other colors mean other things...I want it to add it up
automatically when I change it to green. Hope that makes sense. Is this
possible? Any help would be greatly appreciated.

Let me see if I can describe this... This is my output:

January January February February March March Actuals Plan Actuals Plan Actuals Plan Accounting Finance Etc.

And My input is a data dump into something like this:
Account # 1 2 3 4 5 6 7 1 2 3 4 5 6 7 January January January January January January January January January January January January January January Actuals Actuals Actuals Actuals Actuals Actuals Actuals Plan Plan Plan Plan Plan Plan Plan Accounting 100 20 15 100 4 45 4 Finance 0 0 0 0 0 0 0 Etc. 0 0 0 0 0 0 0

Basically I'm trying to do a lookup function to find To sum all entry's that equal the following critieria:

- Month,
- Actual or Plan, And
- department (i.e. accounting)

I have created an array function that finds all of the corresponding values (at least I think), but I can't get it to then sum only the corresponding values.

This is the equation I have (No reference to above spreadsheets):
=SUM(IF((('Pull _NEW'!$A$8:$A$58=Sheet3!$A8)+(('Pull _NEW'!B5:IE5=Sheet3!B$6)*('Pull _NEW'!B$6:IE$6=Sheet3!B$7))),'Pull _NEW'!B8:H8,"-"))

I've also Tried:
=SUM(IF((('Pull _NEW'!$A$8:$A$58=Sheet3!$A8)+(('Pull _NEW'!B5:IE5=Sheet3!B$6)*('Pull _NEW'!B$6:IE$6=Sheet3!B$7))),(('Pull _NEW'!$A$8:$A$58=Sheet3!$A8)+(('Pull _NEW'!B5:IE5=Sheet3!B$6)*('Pull _NEW'!B$6:IE$6=Sheet3!B$7))),"-"))

FYI: This is a monster spread sheet with mound of data as you can see by the above equations and I don't want to spend hours doing the sum fuction for each cell (would take DAYS!!).

I hope this makes sense.

I am using Microsoft Office Excel 2003. I am looking for a way to format a
cell based upon another cell's format. For instance, if cell A2 has been
filled with a color, then cell F2 should be filled with the same color. I
looked under conditional formats and the only "conditions" are for a cell
having a "value" or "formula." There is nothing about whether the cell
contains a certain "format." Also, one I have all the cells identified as
being filled, I need to be able to sum all the "colored" cells that are found
in a specific range.

In entering data into a large complicated worksheet I would like to have the
active cell be highlighted or outlined in color so that I can easily see
visually where my cursor is. When you are entering in data you have to look
away at your document and then back to the screen and it is sometimes
difficult to quickley see where your cursor is. I only want the active cell
to have this attribute. Thank you.

I have various spreadsheets that have rows hidden with data that I do not want included in the totals. Because of multiple header lines in the worksheets, the use of filters is not practical. Is there a formula that I can use that will sum only the visible cells? Thanks in advance for any help on this.

Hi all
I am using the following table to sum data based on Month and Year the raw data for this will be updated weekly. I am then running other functions off of it what I am looking for is the data in Month table will only update if the month is over (please note once the month is over i will sum only the first 28 days) I am Using Arrays to get this 28 day sum now.
See Cell in yellow for an example the month is not over and I have data that will skew the rest of my calculations

If that is not possible can the other formulas be adjusted to take this in to account and only calculate if the Month is over?

I hope this is clear Please let me know if you have any other questions

Thanks in advance

******** ******************** ************************************************************************>Microsoft Excel - Forecasting.xls___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutF1G1H1I1F2G2H2I2J2K2F3G3H3I3J3K3F4G4H4I4J4K4F5G5H5I5J5K5F6G6H6I6J6K6F7G7H7I7J7K7F8G8H8I8J8K8F9G9H9I9J9K9F10G10H10I10J10K10F11G11H11I11J11K11F12G12H12I12J12K12F13G13H13I13J13K13F14G14H14I14J14F15G15H15I15J15=
FGHIJK12008200920102011AveragePercent by month297727000977968.48%391724000936378.12%4101092000992238.61%581986000934248.10%680501000894247.76%796760000983678.53%878519000919877.98%900001033978.97%1000001074479.32%1100001001278.69%120000925898.03%130000853497.40%1489758---96338 156283090001152767 Trends
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

Is there a function or simple formula to return the sum of the same cell in many sheets? I don't want to have to type in 30 sheet names if I can avoid it.

I need to know the formula for a row with cells filled with diffrent colors and i want to add only particular color cells
for example in B roe only red color cell to be added

Based on a grading system I have color coded certain cells in matrix excel
sheet and I only want to add certain color coded amounts with a formula
instead of going in manually and adding it one by one. Is there any formula
for this??

Hi, all!
Is there a way to sum just the visible cells in a range?
Excel lets one copy or select only visible cells. Can I use an IIF to test a
cell is hiden or not then decide if it should be included in the calculation?


When I use the color cell buttons it does not show up? When I go to print
preview it only shows black and white and not the color I chose?

My workbook is built dynamically... I have N number of sheets and ONE summary
sheet where I want to calculate the sum (for the same cells address) of the
others. By example...

If I have this...
Sheet 1 - Range("A1") = 500
Sheet 2 - Range("A1") = 400

I want SumSheets() function return 900...
Summary Sheet - Range("A1") = SumSheets(ligne();colonne())

That works. But my problem is the value is not refreshed after I change
something in the Sheet 1 or Sheet 2. I tried to place a calculate method in
activate event of the summary sheet, but nothing change. Why the call of the
function is not trigger? And is it a better way to do what I need?
Thank you!

If anyone could help I would greatly appreciate it.

I need to have the 2nd, 3rd, and 4th cell highlighted / colored in the active row.

I need the color to be removed upon navigating to a new row.

The Code I have colors the three cells for me, but it doesn't remove the color when navigating to a new row.

I just need a way to have the background color automatically cleared upon leaving each active row, and clear only those 3 cells. I have conditional formats and colors all throughout the spreadsheet so only the colored cells only need to be cleared.

Current Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim RR As Range
Set RR = Target

Cells(RR.Row, 2).Interior.ColorIndex = 36
Cells(RR.Row, 3).Interior.ColorIndex = 36
Cells(RR.Row, 4).Interior.ColorIndex = 36

End Sub

Paul B

I created a form in Excel 2002 that is distributed to multiple users as a
protected form with locked and unlocked cells that allow users to make
entries in the unlocked cells. Being protected, the form doesn’t allow users
to spell check after completing their entries. I found your correspondence
on-line concerning this issue and used the code that you suggested. This,
however, performs the spell check on the locked cells as well as the unlocked
cells. So, I modified the code to try and only spell check the unlocked cells
as follows:

Sub Spell_Check()

ActiveSheet.Unprotect Password:="123"

CustomDictionary:="CUSTOM.DIC", IgnoreUppercase:=False, AlwaysSuggest:=True

CustomDictionary:="CUSTOM.DIC", IgnoreUppercase:=False, AlwaysSuggest:=True

CustomDictionary:="CUSTOM.DIC", IgnoreUppercase:=False, AlwaysSuggest:=True

CustomDictionary:="CUSTOM.DIC", IgnoreUppercase:=False, AlwaysSuggest:=True

CustomDictionary:="CUSTOM.DIC", IgnoreUppercase:=False, AlwaysSuggest:=True

CustomDictionary:="CUSTOM.DIC", IgnoreUppercase:=False, AlwaysSuggest:=True

ActiveSheet.Protect Password:="123"

End Sub

This progressed thru each range of cells as desired, but after the last
range was checked the spell checker displayed the Dialog Box “Do you want to
continue checking at the beginning of the sheet?” Selecting “Yes” then sends
the Spell Checker through the Locked Cells allowing the user to make a change
each time the spell checker finds a word or abbreviation or acronym that is
not in the user dictionary.

Is it possible to only spell check unlocked cells?

Also, how do I protect the macro from being edited by the user?

Is there a formula that will SUM only the highlighted cells in a column?

See attachment as an example

For example, in column 1 through 3, i only want the sum of the contents in yellow to show up?

Any help is appreciated.


Hi All

I have a feeling that this is a dumb beginners question.

I have a worksheet containing a large column with random coloured (red) cells, how can I Sum only the coloured cells and then only the empty cells?

TIA thalt

No luck finding an answer? You could always try Google.