how to count and sum cells by color in excel

[Patriot2879]

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]

Do you have the add-in loaded?

 

[Sam_D_Ben]

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

 

[Sam_D_Ben]

Do you have the add-in loaded?

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

 

[Patriot2879]

hi sorry no not got any add in

 

[Fluff]

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.

 

[Patriot2879]

oww can you help me with that

 

[Sam_D_Ben]

Also i guess it can be downloded.

 

[Fluff]

Just go to their site & download it.

 

[JugglerJAF]

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)