Oct 12 2017 02:29 PM
Oct 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/ )
Apr 09 2019 05:53 AM
Apr 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!
Apr 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.
Help me!!
Thank you very much!!
Apr 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?
Apr 12 2019 09:21 AM
Apr 12 2019 09:22 AM
Apr 15 2019 09:56 AM
@Jamil Mohammad
Hi Jamil,
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?
Apr 15 2019 10:44 AM
Apr 15 2019 03:17 PM
Apr 15 2019 05:39 PM
Apr 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?
Apr 30 2019 12:21 AM
Hello,
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.
May 06 2019 04:32 PM
May 06 2019 08:18 PM
Hi Jamil,
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.
Thanks again.
May 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)))
May 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.
Thank you.
May 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.
May 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)
May 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.
May 17 2019 12:10 AM - edited May 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 :)