how to count and sum cells by color in excel

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
925
Hi I have the formula below where I am trying to count how many cells have a colour and counting, where my range is to a and the cell colour is yellow which is p1, but the code doesn't work I get a #NAME ? please can u help?

=ASAPCOUNTBYCELLCOLOR(A:A,P1)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,545
Office Version
365
Platform
Windows
Do you have the add-in loaded?
 

Sam_D_Ben

Active Member
Joined
Oct 17, 2012
Messages
381
I guess, ASAP is an Add-in utility, do you have this in Excel?
 
Last edited:

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
925
hi sorry no not got any add in
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,545
Office Version
365
Platform
Windows
In that case that's why you get #NAME? in the cell.
You are trying to call a UDF (user defined function) which you don't have.
 
Last edited:

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
925
oww can you help me with that
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,545
Office Version
365
Platform
Windows
Just go to their site & download it.
 

JugglerJAF

Active Member
Joined
Feb 17, 2002
Messages
264
This is a question that gets asked a lot, and while it is possible to do (using an add-in like ASAP Utilities, or custom VBA code), it's generally not a good idea to do so.

Presumably, each colour is used to indicate that a values that match one or more criteria based on text, numeric, or boolean values in other columns. It's much easier to use SUMIF or SUMIFS and specify the criteria (the same ones that have been used to "flag" the colour used) than it is to use a custom function to sum by colour which is not a native Excel function.

For example, if you want to sum of column B for all of the red cells, but cells highlighted in red are those where department (in Column A) is "Alpha", and sales (in column B) are greater than 5000, then you can use:
=SUMIFS(B:B,A:A,"Alpha",B:B,">="&5000)
 

Forum statistics

Threads
1,078,463
Messages
5,340,458
Members
399,376
Latest member
Tresfjording

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top