<<Convert Excel Spreadsheets to Web Pages \| Trading Software That Operates Within Excel \| Convert Excel, Access & Other Databases \| Merge Excel Files>>

Sum/Count Cells By Fill Or Background Color in Excel

| | Information Helpful? Why Not Donate.

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

Back to: Excel Custom Function/Formulas . Got any Excel/VBA Questions? Free Excel Help

Want to Sum or Count cells that have a specified Fill Color?This can be done with the aid of a Custom Function. A custom function, also called a User Defined Function, is a function that we write ourselves using VBA (Visual Basic for Applications). Here is how

See Also: 2 Separate Functions Here andExcel Sort by Color

First open the Workbook in which you wish to count or sum cells by a fill color. Now go into the Visual Basic Editor via Tools>Macro>Visual Basic Editor (Alt+F11) and then, from within the Visual Basic Editor go to Insert>Module to insert a standard module. Now, in this module, enter in the code as shown below;

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)	Dim rCell As Range	Dim lCol As Long	Dim vResult'''''''''''''''''''''''''''''''''''''''Written by Ozgrid Business Applications'www.ozgrid.com'Sums or counts cells based on a specified fill color.'''''''''''''''''''''''''''''''''''''''  	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 = vResultEnd Function

You can now use the custom function (ColorFunction) like;

=ColorFunction($C$1,$A$1:$A$12,TRUE) to SUM the values in range of cells $A$1:$A$12 that have the same fill color as cell $C$1. The reason it will SUM in this example is because we have used TRUE as the last argument for the custom function.

To COUNT these cells that have the same fill color as cell $C$1 you could use:

=ColorFunction($C$1,$A$1:$A$12,FALSE) or =ColorFunction($C$1,$A$1:$A$12) by omitting the last argument our function will automatically default to using FALSE.

Be aware that the changing of a cells fill color will not cause the Custom Function to recalculate, even if you press F9 (Recalculates the whole Workbook). You will need to either, select the cell and re-enter the formula, or go to Edit>Replace and replace = with =, or use Ctrl+Alt+F9

Try also to avoid the use of Application.Volatile as it will not help in this case and only slow down Excel's calculation time.

See Also: 2 Separate Functions Hereand Excel Sort by Color

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALLpurchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft