10-12-2017 02:29 PM
10-12-2017 02:29 PM
I installed few add-ins for counting "Conditional formatted" cells but all are generating an instant (one-time) numeric result by activating that command.
Also, I tried a code which gives an instant counting of 1 picked color without a total sum of all other colors -in my case 12 colors in a matrix. ("How to use the code to count colored cells and sum their values" - https://www.ablebits.com/office-addins-blog/2013/12/12/count-sum-by-color-excel/ )
04-09-2019 05:53 AM
04-09-2019 08:09 AM
I want to count the number of yellow boxes as in the file below and output in the column "count color yellow"
Thank you so much!
04-09-2019 06:24 PM
I found this code to be used in my file but I want to switch from "Sub" to "Function". That is, I want the result of the yellow box displayed in the column count color yellow.
Thank you very much!!
04-11-2019 10:44 AM
Hi there! I tried adding the formula E$11="" and format is blank but then the COUNTConditionColorCells formula returns #Value! Any idea what I'm doing wrong?
04-12-2019 09:21 AM
04-12-2019 09:22 AM
04-15-2019 09:56 AM
Thank you. Your replies to this post have helped me so much!
I used your code and included Application.volatile. But, I can't get my spreadsheet to automatically calculate the number of coloured cells. It will only update the number of cells if I edit the cell in the formula bar. Is there a way I can set up a button on my spreadsheet that will force excel to calculate how many cells have been coloured?
04-15-2019 10:44 AM
04-15-2019 03:17 PM
04-15-2019 05:39 PM
04-20-2019 05:11 PM
@Jamil Mohammad Hi Jamil, I thank you for your work and effort to help.
But, it seems your code is not working here because I need to see conditional formatting changing colors at columns by the values of the cells and I need to count those color changes at rows direction, after that.
Do you think that it can be done?
04-30-2019 12:21 AM
I have try your VBA formula for sum by conditional formatting color, however formula return "0" result when data are in negative. I did conditionally formatted negative data by using ABS formula. I have staff attendance record which shows short hours by negative sign and excess hours by positive numbers. now I need to sum all short hours and excess hours first by highlighting through conditional format and then sum up those hours.
would you please help me to achieve this.
05-06-2019 04:32 PM
05-06-2019 08:18 PM
Thank you for response.
Sorry I did not make clear what I'm looking for. I do not want all negative to be sum only those cell value which is greater than -1.5 and less than -10.00 should be colored and sum and similarly all the cell value greater than positive 1.5 are to colored and sum.
I have done conditional formatting in attached file and try to apply your formula but its not working. Please help me out with this.
05-06-2019 08:32 PM
In cell CL2 put this formula and drag down =SUMPRODUCT((ABS(BP2:CK2)>1.5)*(ABS(BP2:CK2<10)))
and in CM2 put this formula and drag down =SUMPRODUCT((--(BP2:CK2>1.5)))
05-06-2019 11:09 PM
With this formula it is counting colored cell. For example when I copied the formula to CL2 result showing 22 which is count of color cell (or condition of greater than -1.5 and less than -10). I want sum of all color cell.
05-15-2019 09:05 AM
I am so VERY impressed with your expertise in MS Excel. I'm trying to do this very same task of counting cells based on CF color. I've attempted your code but get "NO-COLOR" as the output. I've attached a dummy file so you can see what's going on.
The only thing I can figure is that it's a different version of excel or I have a setting blocked somewhere.
Thank you so much for your kind consideration and help.
05-15-2019 10:54 AM
if you want to sum instead of count then use =SUMPRODUCT((ABS(BP2:CK2)>1.5)*(ABS(BP2:CK2<10)),BP2:CK2)
05-15-2019 10:58 AM
Thanks for your kind words.
I looked at the file you attached.
If you read my earlier posts, I have mentioned to other users with similar issues as stated below
Hope this helps.
05-17-2019 12:10 AM - edited 05-17-2019 12:12 AM
Hi Mr Jamil
I would like to thank you for this formula. It works great! However, I have a query. Please see attached file. I have a set of KPI’s for disciplines to meet. When I use your formula to count, ‘No of disciplines met by KPI’, it works. However, when I used your formula to count, ‘No of KPI's met by Discipline’, it doesn’t work. May I know what could be wrong? Your help is greatly appreciated :)