EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home Excel Excel Resources Excel Tips Excel Sum by Color

Excel Sum by Color

Ashwani Jaiswal
Article byAshwani Jaiswal
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated August 21, 2023

Sum-By-Color-in-Excel

Sum by Color in Excel

In this article, we will learn about Sum By Color in Excel. In Excel, we have a function for adding numbers. But there is no direct way to add the number by their background color. By this, we don’t need to sum the numbers separating the colored cells. We can directly consider all the cells in the formula and sum them as per their background color.

ADVERTISEMENT
Popular Course in this category
MS Excel Course Bundle - 36 Courses in 1 | 13 Mock Tests

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

This we can do when we have many cell numbers colored, and filtering the data is not suggested there.

How to Sum by Color in Excel?

Excel Sum by Color is very simple and easy. Let’s understand how to sum by color in Excel with some examples.

You can download this Sum by Color Excel Template here – Sum by Color Excel Template

Sum by Color in Excel – Example #1

Here we have data on some product and their sale. As shown below, column C has numbers with some background color.

Example 1

Now go to the cell where we need to see the output and type the “=” sign (Equal). And search and select the SUBTOTAL function as shown below.

Sum By Color Example 1-2

Now, as we need to sum the numbers, so from the drop-down of SUBTOTAL Function, select 9, which is for sum.

Sum By Color Example 1-3

And for reference1, select the complete range of column C, which we need to total as shown below.

Sum By Color Example 1-4

The Output will be as given below.

Sum By Color Example 1-5

Now apply the filter in the top row by pressing Ctrl + Shift +L.

Sum By Color Example 1-6

Go to Filter by Color from the drop-down menu of it. Select any color; we have selected YELLOW, as shown below.

Sum By Color Example 1-7

Once we do that, we will get the Output cell filtered sum as 190, as shown below.

Result of Example 1

We can also check the correctness of the applied SUBTOTAL formula by filtering the different colors.

Sum by Color in Excel – Example #2

There is another way to sum the numbers by their colors. For this, we will consider the same data as shown in example-1. Now copy the column’s cells with numbers and paste them into a separate sheet or in the same sheet in a different location.

Example 2

Now quickly press Ctrl + T. This will enable the selected cells to convert into table format. Now click on Ok from the Create Table box.

Sum By Color Example 2-2

Once we do that, selected cells will convert into the table form. And another menu will add with the name Design in the menu bar. Now Check and tick the Total Row option from the Table Style Options.

Sum By Color Example 2-3

Once we do that, we will get the sum of cells at the bottom end of the column with a drop-down menu. Here we are getting a sum of 786.

Sum By Color Example 2-4

Now from the drop menu of the total sum, select the Sum option as shown below.

Sum By Color Example 2-5

By this, we enable the table to sum the filtered data as per colored cells. Now go to the top filter drop-down of the same column and select any color to get summed up from the Filter by Color option. Select any color; we have selected YELLOW, as shown below.

Sum By Color Example 2-6

Once we do that, we will get the YELLOW colored filtered and the sum of the YELLOW colored cells in the below cell.

Result of Example 2

Sum by Color in Excel – Example #3

There is another method of summing the numbers by their color. VBA Marcos will do this. For this, we will consider the same data we saw in example-1. And we will add separate cells for each product name to get the sum of their quantity sold.

Example 3

Now press Alt + F11 to enter Visual Basic for the Application screen.

Sum By Color Example 3-2

Now go to the Insert menu and select Module.

Sum By Color Example 3-3

This will open a new Module to write code. Now in the blank Module, write the code for enabling the sum by color function in Excel, as shown below. You can also use the same code to make some changes in that.

Example 3-4

Close the complete window of VBA. Now go to the cell reference of Mobile, where we need to see the result and type the “=” sign. Now search and select the Sum Color function we created in VBA.

Example 3-5

And select the reference colored cell and then select the range to get summed, as shown below.

Example 3-6

The Result will be as shown below.

Example 3-7

Once done, drag the formula to complete respective cells to see the result as shown below.

Result of Example 3

As we can see in the above screenshot, the sum of yellow-color cells is coming at 190, which the summed value is obtained in example-1 and example-2. This means that all the formulas and functions used in all examples are correct.

Pros

  • Sum by color from the SUBTOTAL function is the easiest way to get the sum result by color in Excel.
  • The process steps shown in example-2 take a little longer than in example-1, but it is still easy to apply.
  • We don’t need to filter the colored cells separately to get the sum.

Cons

  • Sum by color shown in example-3 by VBA coding takes time, and it doesn’t show the result if we paste the data in another file because it does carry the code with it.

Things to Remember About Sum by Color in Excel

  • If you are summing colored cells by VBA Coding, it is always recommended to save in the Macro enabled Excel; this will save the coding for future use.
  • These methods can use anywhere, irrespective of the data size. It is always recommended to use this method when we have a huge set of data, where if we filter the data to get the summed value may crash the file.

Recommended Articles

This has been a guide to Sum by Color in Excel. Here we discuss how to sum by color in Excel, practical examples, and a downloadable Excel template. You may also look at the following articles to learn more –

  1. Color in Excel
  2. Excel Sort by color
  3. VBA Color Index
  4. Count Colored Cells In Excel
ADVERTISEMENT
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
MS Excel & VBA for Data Science Course Bundle - 24 Courses in 1 | 10 Mock Tests
87+ Hours of HD Videos
24 Courses
10 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
All-in-One Financial Analyst Masters Training Program Bundle - 550+ Courses | 300+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.9
ADVERTISEMENT
All-in-One Data Science Bundle - 400+ Courses | 550+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.7
Primary Sidebar
Footer
Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
  • Blog as Guest
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Sum by Color Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

EDUCBA

Download Sum by Color Excel Template

EDUCBA

डाउनलोड Sum by Color Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Cyber Monday Reloaded Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW