EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

Ozgrid, Experts in Microsoft Excel Spreadsheets

2 Excel Functions/Formulas to Count/Sum Excel Cells by Color

 

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

This UDF will sum a range of cells based on their fill colour

SEE ALSO: Single Function to Sum Or Count Cells by Their Fill Color

The Code:

Function SumColor(rColor As Range, rSumRange As Range)



''''''''''''''''''''''''''''''''''''''

'Written by Ozgrid Business Applications

'www.ozgrid.com



'Sums cells based on a specified fill color.

'''''''''''''''''''''''''''''''''''''''

	Dim rCell As Range

	Dim iCol As Integer

	Dim vResult



	iCol = rColor.Interior.ColorIndex



		For Each rCell In rSumRange

			If rCell.Interior.ColorIndex = iCol Then

				vResult = WorksheetFunction.Sum(rCell) + vResult

			End If

		Next rCell



	SumColor = vResult

End Function

Or, this UDF will count a range of cells based on their fill color

The Code:

Function CountColor(rColor As Range, rSumRange As Range)



''''''''''''''''''''''''''''''''''''''

'Written by Ozgrid Business Applications

'www.ozgrid.com



'Counts cells based on a specified fill color.

'''''''''''''''''''''''''''''''''''''''

Dim rCell As Range

Dim iCol As Integer

Dim vResult



iCol = rColor.Interior.ColorIndex



   For Each rCell In rSumRange

    If rCell.Interior.ColorIndex = iCol Then

         vResult = vResult + 1

    End If

   Next rCell



CountColor = vResult

End Function

To use these UDF's push Alt+F11 and go Insert>Module and paste in the code. Push Alt+Q and save. The Function will appear under "User Defined" in the Paste Function (Shift+F3).

Use the Function as shown in the graphic example below.

Amount
  $     10.00  
  $     25.00  
  $     66.00  
  $     25.00  
  $     31.00  
  $     22.00  
  $     65.00  
  $   100.00  
  $   200.00  
Result
$   135.00
 
Formula =SUMCOLOR(B2,B1:B10)

B2is the cell containing the fill color wanted (yellow).
B1:B10 are the cells to sum IF they have the same fill color as B2

See Also: Excel Duplication Manager Add-in | Excel Number Manager Add-in | Excel Text Manager Add-in | Excel Named Range Add-in Manager | Excel OzGrid Plus Add-in | Excel Time Sheet | Excel Time Wage and Pay book

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. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.


Instant Download and Money Back Guarantee on Most Software

EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

Excel Trader Package Technical 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

Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates