Suppose during working on Excel, if you get a worksheet in which there are different kind of background colors in range of cells. Now if someone ask you how to sum or count cells based on background color in Excel. It is not an easy task to get the proper answer in quick way. In this guide we have to discuss how to sum or count cells based on background colors in Excel.
Sometime you need to use sum or count certain cells which is based on the background color in Excel. By default Excel does not have any formula to calculate the sum or count cells based on background color in Excel. In that situation, if you do this job manually then it will take lot of time. But if you don’t have enough time and want to get quick result then what you have to do? Now, there is no need to worry in this guide we have to discuss how to sum or count cells based on background color in Excel.
Must Read: How to Search Duplicate Values with VLookup function
For eg. If you have any worksheet which have range of cells with lot of background colors. Now, you need to sum or count cells which is based on the background color in active worksheet. In that situation there is no particular function which solve your problem. So, we have to discuss an easy way which helps you how to sum or count cells based on background colors in Excel.
How to Sum or Count cells based on background color in Excel
Step 1: Create the following table in any worksheet which have different background colors on range of cells.
Step 2: If you want to get sum or want to count given range of cells which is based on the color then first you have to set the cell color name in cell D2. You can also create cell color list with the help of Data Validation command in Excel. Now there is no need to type again and again cell color name.
Step 3: To get the sum of given range of cells which is based on the color you have to type sumif function in cell D3
=SUMIF(B2:B16,E2,A2:A16).
Must Read: What is Difference between SUMIF and SUMIFS Function
If you want to count the given range of cells which is based on the color then you have to use countif function in cell D4
=COUNTIF(B2:B16,E2).
Note: You have to choose required cell color from the drop down list in cell D2. Now, you can get the required result which is based on the cell color.
It is an effective article which not only easier for you but also reduce your working time during manually calculation. I hope after reading this guide you can easily sum or count cells based on background colors in Excel. If you have any suggestion or query regarding this guide then please write us in the comment box. Thanks to all.