Dreevoo.com | Online Learning and Knowledge Sharing
 
Home | Programs | Microsoft Office | Microsoft Excel | How to sum cells according to their font color in Excel
Guest
Click to view your profile
Topics
Programs
Languages
Recipes
Home
Shortcuts
 
 

How to sum cells according to their font color in Excel

You will find out how to summarize cells that have a same color of the font, the red  one in my case. Really useful when working with different types of specific data in Excel.

 
  Author: NikMan | Version: 2010 | 27th August 2013 |  
 
 
1.
 

Retype my data in your new Excel spreadsheet.

 
 
2.
 

After that press Alt + F11 keys together to open Microsoft Visual Basic for Applications window.

Click on Insert and choose Module.

 
 
3.
 

Enter the following VBA code:

Function sumcolor(ByVal ref As Range)
Dim cl As Range
sumcolor = 0
For Each cl In ref
    If cl.Font.Color = vbRed And IsNumeric(cl) Then sumcolor = sumcolor + cl
Next
End Function

 
 
4.
 

Close the window.

 
 
5.
 

Click on random empty cell.

 
 
6.
 

Type the following formula:

=sumcolor(A1:A5)

A1:A5 means cells from A1 to A5.

 
 
7.
 

Press Enter key and the cell will summarize all the cells with red font inside.

When data will be modified, you will have to select the cell with formula and confirm it again to refresh the result.

 
 
 
   
  Please login to post a comment
   
 
 
online learning made for people
Dreevoo.com | CONTRIBUTE | FORUM | INFO