We will be going to see the new user-defined Excel Functions (UDF). These functions can sum by Background Color or Font Color as well as can count cells by Background color or Font Color means sum by color and count by color.
If you work with Excel and often use color to organize your data, you know how challenging it can be to sum or count cells based on their color. Luckily, you can simplify this task with a custom User Defined Function (UDF). In this article, you’ll learn:
- How to create a UDF in Excel that sums and counts cells by their background color.
- Step-by-step instructions to implement the ‘Sum by Color’ and ‘Count by Color’ functions.
- Practical examples of how these functions can streamline your data analysis.
- Tips and tricks for getting the most out of color-coded data in Excel.
By the end of this guide, you’ll be able to quickly and efficiently manage your color-coded data, making your Excel workflow smoother and more productive.
Contents
What are these functions?
The following are the UDF Functions –
- SumByColor_UDF (criteria, Range)= To Total of Value, where Background color matches.
- SumByFontColor_UDF (criteria, Range) = To Total of value, where Font Color Matches.
- CountByColor_UDF (criteria, Range) = To Count Cells, where Background Color Matches.
- CountByFontColor_UDF (criteria, Range) = To Count Cells, where Font Color Matches.
Does this function exist in Excel?
No, this function does not exist in Excel. This is a Customized Function. Follow below given steps to add this function.
What are the inputs, we have to provide to get the Result?
- CRITERIA = Color Criteria, We want to match for Sum or Count (Background Color or Font color).
- RANGE = Range of Dataset, we want to search in and get Results from.
How to use these new UDF Functions?
We will see an example, how to use these UDF Functions.
We have Dataset in F4 to J13. We want output in Column-C and Color Criteria in Column-B.
In Cell C4, we will Enter Function = SumByColor_UDF (criteria, Range).
Press “Ctrl + Shift + A” Inside the Function Bracket to See the Variables.
Criteria will be Cell B4. Range will be F4:J13. See image below.
We will get the result, as shown in image below.
How to add this Functions to Excel?
Follow the steps below to activate these functions –
Go to “File”>> “Options” >> “add-ins” Option >> Select “Excel Add-ins” & Click “Go” >> click “Browse” >> Copy in add-ins in the Window. >> Click “OK” >> Tick Mark New Add-ins Name >> It’s Done.
Now This function will be Activated for All Excel Workbook.
Which Add-ins Need to be added to Activate Functions? (Download Link)
Free Download Link is provided for Add-ins (Sum by Color).
Download Below Add-ins to activate Functions.
Video Demonstration
Your Suggestions for future development are always welcome, Feel free to write in Comment below.
