Many times, we use different background fills for our cells to denote something. However, when it came to apply formulas on these colored cells, its not possible.
Here, I will demonstrate a way, through which we can indirectly apply any excel formula for colored cells, whether it is Sumif, Countif or even V Lookup.
Now, in this above list, I want to get the sum of all Yellow colored cells. To do this, we will perform the below steps:
Go to Formulas tab and select "Name Manager" followed by clicking on "New" as highlighted below.
After clicking "New", we have to perform the below step:
Give a Name in the "Name" field. Here I am giving it a name "Get_color" as shown above.
In Refers to: field we will put a formula as "=GET.CELL(38,Sheet2!$A1)"
Now as my data is in Sheet2 "A" column so I have kept the column fixed but row number is not fixed.
After this we will click "ok", followed by "close"
So our formula is created to identify cell color. See below:
We will get a specific number assigned to a particular color, In this case we got "6" for yellow color, "3" for red color.
So, now as we have a number associated with a different color, we can very easily apply a Sum If formula to get the total of all cells in "Yellow" color.
