how to count and sum cells by color in excel

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,227
Office Version
  1. 2010
Platform
  1. Windows
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)
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Do you have the add-in loaded?
 
Upvote 0
I guess, ASAP is an Add-in utility, do you have this in Excel?
 
Last edited:
Upvote 0
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:
Upvote 0
Just go to their site & download it.
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top