Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How To Count Or Sum Cells Based On The Font Colors In Excel?
Excel is a powerful tool that allows users to perform a wide range of tasks, from simple calculations to complex data analysis. One of the many features that makes Excel so versatile is the ability to use formatting to help organize and analyse data. In this tutorial, we will focus specifically on how to count or sum cells based on the font colors used in them. This can be a useful tool when working with large data sets or when trying to quickly identify trends or patterns within a data set. Whether you are a beginner or an experienced Excel user, this tutorial will provide step-by-step instructions to help you master this useful skill.
Count Or Sum Cells Based On The Font Colors
Here we will first create a formula using the VBA application and then use it to complete the task. So let us see a simple process to know how you can count or sum cells based on the font colours in Excel.
Step 1
Consider an Excel sheet where you have data represented in different font colours, similar to the below image.
First, right-click on a sheet name and select view code to open a VBA application, then click on insert and select module, then copy the below-mentioned code into the text box as shown below.
Right click > View code > Insert > Module > Copy code.
Public Function CountColour(pRange1 As Range, pRange2 As Range) As Double
Application.Volatile
Dim rng As Range
For Each rng In pRange1
If rng.Font.Color = pRange2.Font.Color Then
CountColour = CountColour + 1
End If
Next
End Function
Step 2
Now to count the red colour font, click on an empty cell in our case cell G2 and click enter to the formula =CountColour(A1:C6,F2) to get the result.
Step 3
Then, to count all the remaining colours, use the formulas as =CountColour(A1:C6,F3), =CountColour(A1:C6,F4) to count blue and green, respectively.
Step 4
Now, to calculate the sum based on font colour, use the below VBA code.
Public Function SumByColor(pRange1 As Range, pRange2 As Range) As Double
Application.Volatile
Dim rng As Range
Dim xTotal As Double
xTotal = 0
For Each rng In pRange1
If rng.Font.Color = pRange2.Font.Color Then
xTotal = xTotal + rng.Value
End If
Next
SumByColor = xTotal
End Function
Then use the formula =SumByColor(A1:C6,F2) and repeat the above steps, and our final result will be similar to the below image.
Conclusion
In this tutorial, we used a simple example to demonstrate how you can count or sum cells based on the font colours in Excel to highlight a particular set of data.
3K+ Views
