# how to count and sum cells by color in excel

#### Patriot2879

##### Well-known Member
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)

#### Sam_D_Ben

##### Active Member
I guess, ASAP is an Add-in utility, do you have this in Excel?

Last edited:

#### Sam_D_Ben

##### Active Member

Hey Fluff, I dint see your comment. I am sorry.

#### Patriot2879

##### Well-known Member
hi sorry no not got any add in

#### Fluff

##### MrExcel MVP, Moderator
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
oww can you help me with that

#### Sam_D_Ben

##### Active Member
Also i guess it can be downloded.

#### JugglerJAF

##### Active Member
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)