Save 5 minutes a day by using ASAP Utilities to quickly count or sum cells based on their background color.
"Did you ever have to SUM by Color or Count by cell Color?"
I have a column that needs summed, but only the cells that are green...
Working with cell colors can help to make your Excel workbook easier to understand. Many people also use it to mark cells.
Unfortunately Excel has no built in functions to use cell colors as conditions in formulas. Constructing formulas that only count or sum cells with certain colors can become really cumbersome. It often results in complex formulas that are prone to errors when changes are made.
Fortunately with ASAP Utilities it is easy to do.
Please see below example:
You can COUNT or SUM the cells with a certain color with the following functions:
- =ASAPCOUNTBYCELLCOLOR(SearchRange, Color)
Counts the number of cells in the given range that have a certain fill color. - =ASAPSUMBYCELLCOLOR(SearchRange, Color)
Sum the values in the cells that have a certain fill color.
The following page shows a list of all the functions that are available in ASAP Utilities:
ASAP Utilities » Formulas » Insert function from the ASAP Utilities library...
Remarks on calculation
Since Excel only recalculates a formula if the value in a (referenced) cell changes and not when the colors are changed, some of the ASAP Utilities worksheet formulas don't always get triggered to update. In that case you can use Excel's shortcuts F9 or Control+Alt+F9 to have the ASAP Utilities formula(s) updated.
Remarks on distributing
If you use these worksheet functions in your worksheet there a few considerations to make if other people are going to work on the same workbook:
- Everybody that works with the file using the ASAP Utilities worksheet functions should have ASAP Utilities installed.
- If somebody is going to work with your file but he/she doesn't have ASAP Utilities, then you should convert the formula-results to their values (Copy » Paste Special » Values) or in ASAP Utilities:
ASAP Utilities » Formulas » Change formulas to their calculated values
If you see the result of a function displayed as "#NAME?" this means that ASAP Utilities is not available or installed in another folder. (In case you do have ASAP Utilities installed it can also mean that there is a typing-error in the formula name).
Do you recognize any of these situations?
- Not sure if ASAP has this feature, but it would be useful to be able to count colored cells in a range. Say I have 17 pink colored cells in range B1:B500. I would like formula that would count these 5 pink cells, even if they have no text or data in them.
- Can a color be used as a condition of SUMIF? I have a column that needs summed, but only the cells that are yellow.
- I noticed that in recent Excel versions it is possible to sort by cell and font colours. What about Excel function? Can the formulas work on cell and font colours too?
- How can I count or sum cells by their fill or background color? There seems to be no formula in Excel that allows to count or sum a range of cells based on a specified background/shading color.
- Several of my colleagues have been tracking their work by manually coloring the cells based on who did the particular task (ex. John colors the cells he's worked on blue, Suzan colors hers red, Daniel colors his yellow).
I need to get a total of each color. I'm looking for something automated so we don't have to sort by color every time to get a total. The issue seems to be that they're manually coloring the cells, so there's no criteria to base a sum off of, other than the colors.
Just use one of the following functions:
- =ASAPCOUNTBYCELLCOLOR(SearchRange, Color)
Counts the number of cells in the given range that have a certain fill color. - =ASAPSUMBYCELLCOLOR(SearchRange, Color)
Sum the values in the cells that have a certain fill color.
See also the additional functions that are available:
ASAP Utilities » Formulas » Insert function from the ASAP Utilities library...
Download example workbook that demonstrates these extra functions
We have created an example workbook that shows how to get things done, that are normally not possible in Excel, by using the functions that ASAP Utilities adds.
Bonus tips, also interesting
How much time will it save?
It's guaranteed that you'll save yourself time and effort by using this tool. However, the actual time saved depends on how much you use Excel, the amount of data you are working with and how often you use this particular tool.
You can easily see how much time ASAP Utilities has saved you so far.
Download
In case you don't have ASAP Utilities yet, you can download the free Home&Student edition (for home projects, schoolwork and use by charitable organizations) or the fully functional 90-day Business trial.
Download page