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,247
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,247
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,247
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,077,849
Messages
5,336,736
Members
399,100
Latest member
darcob

Some videos you may like

This Week's Hot Topics

Top