As we all know that MS Excel has no formula or feature to calculate such a thing to calculate cells on their background color. Here, explained one methods that will help you to achieve this.
To make the task more clear let’s make a sheet as per below image.
Here we don’t need the total sum of all elements but we need the sum of elements that have the same background color.
Steps: This steps is much faster and better.
For this task we can use a small UDF (User Defined Function) which will fo the trick for us. This function does not return the color name but it returns the color index which is also a unique value and can be used in our task.
- Open Worksheet
- Fill as per above background color or whatever you want
- Press Alt + F1 to open the VBA or Right click on sheet name and select View Code.
- Navigate the Insert > Module
- Type the following ‘SumByColor‘ UDF in the editor
=SumByColor(Cell_with_background_color_that_you_wish_to_sum, Range_to_e_summed_up)
- Now enter the function ‘SumByColor‘ as formula
- After that, drag this formula to the whole range.
So, This is all from this topic.
Learn & Acquire 