This tutorial talks about how to count and sum cells by background or font color in Excel. I will use an Excel utilities package called Asap Utilities for this. Asap Utilities makes it pretty easy and all the functions can be performed by just a few clicks. By just providing the range and color index of the source cells, you can easily sum or count them using the predefined functions of Asap Utilities.
Sometimes it can be handy to count and sum cells by background or font color in Excel in case you are dealing with colorful tabular data. The methods explained in the following tutorial do the same using various functions of Asap Utilities.
Let’s see what it takes to count and sum cells by background or font color in Excel.
How To Count And Sum Cells By Background Or Font Color in Excel?
Asap Utilities is a software package that is full of such Excel tweaks and tricks. This software package is really handy to save valuable time of its users. Though, it is a Paid software, but it’s free for home and educational use. And in the below sections, I will make use of some of the functions of Asap Utilities to achieve my goal.
It’s fairly easy to use it as it adds an extra tab in Excel’s window, where you can find all the functions and utilities offered by it. You can download and install this software using the link given above. And its interface looks like the following screenshot.
To see the usage of various functions of Asap Utilities to count and sum cells by background or font color in Excel, go through the tutorial below.
How to Count Cells in Excel by Background or Font Color?
Count Cells by Background Color in Excel:
In this section I will show you how you can use the Asap Utilities package to count cells by background or font color. The Asap utilities package is full of such functions and I will use two of them. To see how to do it, just follow these simple steps.
Step 1: Open any Worksheet that contains some colored cells in it. Click on any blank cell where you want to put the result.
Step 2: Now, navigate to the Asap Utilities tab in Excel. And from the Formulas drop down choose the Insert function from Asap utilities library option.
Step 3: After that, a dialog will appear containing lots of formulas. From the same list, choose ASAPCOUNTBYCELLCOLOR function and hit OK.
Step 4: Now, a dialog will appear asking you to specify the range of cells and cell whose background color you want to use. Specify them both and click OK.
After clicking on the OK button, you will see that the count value of the cells of that background color will appear in the result cell.
So, in this way you can easily count cells by background color in Excel. And it was pretty easy to do that by using the Asap Utilities package.
Count Cells by Font Color in Excel:
In the above section I have explained how to count cells by background color. In this section I will show how to count cells by their font color. The process is pretty much similar to what I have explained in the above section. To do this, open up any worksheet that has colored data in it and select a cell where you want to put the result. After that, from the formula drop down of Asap Utilities tab, choose Insert function option. After that, a list of formulas will appear, choose ASAPCOUNTBYFONTCOLOR from it.
Now, a dialog will appear asking you to enter the range of the cells and cell index whose font color you want to use in the formula. After specifying these parameters, hit the OK button. And you will see the number of cells of that font color.
So, that was the case with counting cells in excel by their font color. And using Asap Utilities, you can do that in just a few clicks.
How to Sum Cells by Background or Font Color?
Sum Cells by Background Color in Excel:
In the previous section I have shown you how to count cells by background or font color in Excel. In this section I will show you something more interesting. Here, we will see how to sum cells by background color in Excel. There are some other functions in Asap Utilities that can do it in just a few clicks.
To sum cells by background color in excel, follow these simple steps.
Step 1: Open an Excel sheet having some colored cells and values in them. Also select a particular cell where you want to display the result.
Step 2: Choose Insert function from Asap Utilities option from the formulas drop down of the Asap Utilities tab.
Step 3: A list of functions will be displayed there. Scroll down the list, and choose the ASAPSUMBYCELLCOLOR formula from it and hit OK button.
Step 4: After that a dialog will appear asking you to specify the range of the cells and cell index whose background color you want to use in summing. After specifying these parameters, hit the OK button.
At the end of the step 4, you will see the resulted value will be displayed in the result cell.
So, in this way you can sum cells on the basis of their background color using Asap utilities. And all the process hardly takes a few seconds to complete.
Sum Cells by Font Color in Excel:
This method covers how to sum cells by font color in Excel. In the previous method we summed up cells based on the background color. And in this method we shall how to sum cells by font color in Excel. Using the Asap Utilities, you can also do that in a very short amount of time.
The process of summing up the cells based on font color is similar to the above method, we just have to choose the different formula here. To do this, open up a worksheet that has some colored cells with a value assigned to them. After that, choose ASAPSUMBYFONTCOLOR from formulas drop down of the Asap Utilities tab.
Now, a dialog will pop up where you have to specify the range of cells and cell index of the source cell that has a desired font color in it. After that, hit the OK button, and you have done it. The resulted value will be displayed in the result cell.
So, in this way you can sum cells by font color in Excel. Asap Utilities makes it pretty easy to do that in a very smart and efficient way.
Closing Thoughts
In the tutorial above, I have demonstrated how to count and sum cells by background color in Excel. Using the Asap Utilities package, you can easily do that in just a few clicks. And what I really like about this software is that while using it you are given such an interface that you will think you are working with built-in Excel functions.
So, if you are looking for ways to do the same, then this tutorial will help you. Use Asap Utilities to count and sum cells by background color in Excel, and I am sure you will like it for its simplicity and functionality.
