7 Easy Ways to Sum Colored Cells in Excel Without VBA

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.

Data Sheet for the problem of sum colored cells in excel without vba

Method

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.

Using SUMIF function in sum colored cells in excel without vba

  • Finally, we press Enter and get the total mark of Robin.

Method

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.

Inserting to table

  • Secondly, we will find a Create Table dialogue box. We choose the “My table has headers” option and then press OK.

Creating a Table for sum colored cells in excel without vba

  • 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.

Using Filter option

  • 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.

Use of SUBTOTAL function in sum colored cells in excel without vba

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

Method

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 ⬇️⬇️

  • After filtering our dataset we select the Mark header then select Filter by Color and select our color.

Filter by 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.

Using SUBTOTAL function

📕 Read More: How to Add Specific Cells in Excel (5 Easy Ways)

Method

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.

Method

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.

Using Find and Select tool for sum colored cells in excel without vba

  • 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.

Find all process in Excel

📕 Read More: 4 Easy Ways to Add a Range of Cells in Excel

Method

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.

Use of Name Manager for sum colored cells in excel without vba

  • 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.

Use of Color Code for sum colored cells in excel without vba

  • 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

Method

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.

Use of COUNTIF function for sum colored cells in excel without vba

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.

Use of Sort and Filter in count colored cells using formula

  • 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.

 

 

 

 

 

(Visited 47 times, 1 visits today)
Md. Ahaduzzaman

Md. Ahaduzzaman

Hello everyone i'm Md.Ahaduzzaman.I am a civil engineering graduate from BUET.I am enthusiastic and highly motivated to always learn something new to broaden my horizon and find my lacking. Hopefully my Articles will benefit you to understand and overcome your problem.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo