Md. Ahaduzzaman
Sometimes we need to process data in Excel quickly by coloring them. We may want to count the number of colored cells or the values of the cells. Although VBA can be an option for this it is not so easy. Moreover, it can be very tricky to solve a problem by using VBA. Therefore here we are going to see 7 easy and efficient ways to sum colored cells in excel without vba.
📁Download Excel File
Download the free workbook for your practice:
Learn to Sum Colored Cells in Excel Without VBA by These 7 Methods
In order to demonstrate the ways to sum colored cells in Excel without VBA we are using a simple dataset of the mark sheet of students of a particular class. We are trying to know a student named Robin’s mark and entry number. 7 different ways to sum colored cells in excel without VBA are given in the following sections. The dataset is given below.
1. Applying SUMIF Function
Here we are using the SUMIF function to calculate the total marks of Robin during three different terms. Therefore the step-by-step procedure is shown below.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly here, we want to know the total mark of Robin in cell D13.
- In the next step, we select the cell D13 and type the following formula:
=SUMIF(B6:B11,”Robin”,D6:D11)
- Here SUMIF function sums up all the data entries from column D if it matches the criteria of the name Robin from column B.
- Finally, we press Enter and get the total mark of Robin.
2. Using SUBTOTAL Function
Here we are using the SUBTOTAL function to again determine the total marks of Robin in three different terms in his class. So the step-by-step procedures are given below.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, we select our data range. Then we go to Insert then select the Table option.
- Secondly, we will find a Create Table dialogue box. We choose the “My table has headers” option and then press OK.
- In this step, we click on the filter arrow at the header cell of column Mark and choose the Filter by Color option, and select our color.
- Finally, at this point, our table is filtered. At cell D13, we write this formula in the formula bar to get our result.
=SUBTOTAL(109, D5:D11)
🔨 Formula Breakdown
👉 Here 109 is the SUM function which sums the value from D5 to D11.
👉 Here SUBTOTAL function gives us the sum of all colored cells at column D in cell D13.
Differences Between SUBTOTAL and SUM Functions
There is a difference between the SUBTOTAL function and the SUM function even though both of them give us a summation of a data range. The SUM function only includes the filtered data while calculating the summation. But the SUBTOTAL function doesn’t include the filtered data of the data range. Clearly, it only takes the visible data into account.
📕 Read More: 5 Effective Ways to Sum Random Cells in Excel
3. Applying FILTER Feature Based on Cell Color
Here we are going to use the Filter tool based on the color cell to determine the total marks of the colored cell mark, Robin. Therefore the step-by-step procedures are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we select our data range. Then we go to the Sort and Filter option and select Filter.
- After filtering our dataset we select the Mark header then select Filter by Color and select our color.
- Finally, in this step at cell D13, we insert this formula to get our result. Here 109 is basically the SUM function.
=SUBTOTAL(109, D5:D11)
🔨 Formula Breakdown
👉 Here 109 is the SUM function which sums the value from D5 to D11.
👉 Here SUBTOTAL function gives us the sum of all colored cells at column D in cell D13.
📕 Read More: How to Add Specific Cells in Excel (5 Easy Ways)
4. Applying FILTER Tool and SUM Function
Here we are going to use the Filter option and SUM together to determine the sum colored cells in excel without VBA. So the step-by-step procedures are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly here in cell D13, we put this formula in the formula bar.
=SUBTOTAL(103, B6:B11)
🔨 Formula Breakdown
👉 Here 103 is the COUNTA function which counts the entry from B6 to B11.
👉 Here SUBTOTAL function gives us the sum of all colored cells entry at column D in cell D13.
- Finally, we again Filter our dataset as shown in previous methods and get our result in cell D13.
5. Using Find & Select Tool
Here we are going to learn how to use a feature called FIND & SELECT to determine the number of colored entries in a dataset. So the step-by-step procedures to sum colored cells in excel without VBA using FIND & SELECT are shown below.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, we select our data range. Then we go to the FIND & SELECT option and select the FIND option.
- A dialogue box appears. Here we select the Format option.
- Then we get the Find Format dialogue box. We select the Fill option and choose our color and then select OK.
- Finally, we select Find All All the colored cell entry results are shown below.
📕 Read More: 4 Easy Ways to Add a Range of Cells in Excel
6. Applying GET.CELL Function
Here we are going to show how to use GET.CELL function to determine the total number of Robin which is denoted by colored cells. So the step-by-step procedures to sum colored cells in excel without VBA by are given below.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, we go to the Formulas Then we select Name Manager.
- Secondly, we get a dialogue box. We select the New option.
- We give it the name ColorCode. We put in the Refers to box the given formula.
=GET.CELL(38,’Method 6’!$D6)
🔨 Formula Breakdown
👉 38 returns the color code of the cells in column D.
👉 The name of our datasheet is Method 6.
👉 Column reference D is fixed by !$D6 but the row remains unclocked.
- Here we get the color code of our entry by color in E6.
- We use the Fill Handle tool to drag the formula from E6 to E11.
- Finally, in cell E13, we put the formula given below. Finally, we get our result in cell E13.
=SUMIF(E6:E11,ColorCode,D6:D11)
🔨 Formula Breakdown
👉 Here ColorCode gives us color code return from E6 to E11.
👉 SUMIF function sums all the entries of marks in column D if they have any color code value.
📕 Read More: 5 Ways to SUM Last 5 Values in Row in Excel
7. Using GET.CELL and COUNTIF Functions
Here we are going to use GET.CELL and COUNTIF function to sum colored cells in Excel without VBA.So the step-by-step procedures are given below.
⬇️⬇️ STEPS ⬇️⬇️
- From the previous method, we again create the ColorCode. Then in D13, we write this formula in the formula bar. We get our return value in cell D13 after pressing Enter.
=COUNTIF(E6:E11,ColorCode)
🔨 Formula Breakdown
👉 ColorCode gives us color code return from E6 to E11.
👉 COUNTIF function counts all the entries of marks in column D if they have any color code value.
When Shouldn’t You Use GET.CELL?
GET.CELL function is a great tool to sum colored cells in Excel without VBA. Therefore we must use it carefully. We can’t directly access the function when we write the formula. We have to define a custom function at first. Also, GET.CELL doesn’t work if we apply background color using conditional formatting.
How to Count Colored Cells in Excel Using Formula
Here we are going to see how to count colored cells in Excel using a formula. For example, we are coloring the cell in red. That means we are counting cells using the formula if the cell color is red. So the step-by-step procedures are given below.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, we are putting this function in cell D13.
=SUBTOTAL(103, D6:D11)
🔨 Formula Breakdown
👉 In here 103 is the COUNTA function which counts the entry from D6 to D11.
👉 SUBTOTAL function gives us the count of all cell entries at column D in cell D13.
- Secondly, we select our data range. Then we go to the Editing option and select Sort & Filter menu then we will select the Filter option.
- Here at cell B5 we select the dropdown option and choose Filter by Color. Then, we select the color red.
- Finally, we will see in column B all the red-colored cells and in cell D13 we get the sum of the total red-colored entry.
📕 Read More: 3 Quick Approaches to Sum Top N Values in Excel
📄 Important Notes
🖊️ Firstly, we have to save the Excel file as a Macro Enabled workbook when we use GET.CELL function.
🖊️ Finally, we have to fix the cell reference properly.
📝 Takeaways from This Article
📌 We can use 7 different ways to sum colored cells in Excel without VBA.
📌 Also by using GET.CELL function we can create our own custom-made function.
Conclusion
Here to sum colored cells in excel without VBA we learned 7 different methods. We can use SUMIF, SUBTOTAL, FILTER, FIND AND SELECT or GET.CELL function. We can use whatever you like. Also, readers can comment in the comment section if they have any queries. The writer will try their best to come up with a solution. Last but not least follow Excelden for more Excel-related queries.
