Sum Cells by Color in Excel using VBA – ExcelVbaAutomation

Sum Cells by Color in Excel
  • Save

Introduction/Problem Statement

In this blog, we will talk about how to Sum Cells by Color the value of all cells of a particular range that are a particular color. There is no SUMIF function in excel to sum based on color.

Solution

This code will Sum Cells by Color the value of the range A2 to A28 to if the cell background color is yellow and paste sum value to range G5.

Sum by color
  • Save

Function to Sum Cells by Color

There is no SUMIF function in excel to sum based on color. If a user wants to sum based on color, then we must create a function. I have created a function for this you can simply paste this code in a module in Visual basic editor and save the workbook in “XLSM” or “XLSB” format.

To use this function type =SumBycolor(A1:A10,B4) in cell – where A1:A10 is the sum range and B4 is the cell reference that contains the background color that is our criteria.

Function to sum by colour
  • Save

Conclusion

This blog is very important for MIS reporting because in many cases users need to Sum Cells by Color of the range which contains a specific color. We have created a function above which you can use directly only after pasting code in the module of the VBA code window.

Leave a Reply

Your email address will not be published. Required fields are marked *

Copy link
Powered by Social Snap