Dec 20 2016
01:16 PM
- last edited on
Jul 25 2018
09:33 AM
by
TechCommunityAP
Dec 20 2016
01:16 PM
- last edited on
Jul 25 2018
09:33 AM
by
TechCommunityAP
i have a column of numbers that are color coded (to represent a specific mfg department) and I need to total the values by color. Meaning I need to total all the values that have the same background color.
I have done an exhaustive search online and was able to quickly find a way to sum by color, unfortunately it only worked for cells whose color was set manually (NOT using conditional formatting). I have found a few references to VB code that should provide the functionality but I can't get any of them to run (except for the one that works for manually set colors).
I have control of the data that I'm trying to sum. is there another method to "tag" values? I thought of adding a letter prefix, but coulnd't find any way to sum a column of numbers that are contained in text strings.
Any help would be greatly appreciated!!
I'm using Office 365 (excel 2016) on a windows 10 machine
Nov 26 2018 10:01 AM
Nov 26 2018 04:55 PM
Jamil,
Thanks for replaying.
It's true... on a previous post I said that the code you gave me works and I stand by that.
The request after that was of a different scenario... the formulas need to be applied in a totally different way...
Also, I already deleted all the CF... the file I send earlier belongs to a bigger workbook where all that conditional formatting is used and some how it stayed (I forgot to check and clean) in this file and the CF even linked to a Sheet4... The CF in the file now is only what's needed.
But no worries... I already figure it out...
Please see attached WORKING file... I upload it here for reference in case any one else might need the same solution.
If you inspect the file, you will see that is not the same problem although is has to do with counting with conditional formatting... which is what this thread is about.
Thanks again Jamil.
Nov 27 2018 05:03 AM
Dec 05 2018 02:28 PM
Hi Jamil,
I would like to count the number of coloured cells in a set of data regardless of the numbers within the coloured cells. These colours are determined by conditional formatting. The colours include green, amber and red.
I have applied your VBA code to count colours, however it does not count correctly. It only counts the amber coloured cells from the data set.
From the dummy data I have attached, the number of reds should be 2, the number of ambers should be 1 and the number of greens should be 2.
Could you please have a look at the dummy data attached to determine why there is an issue and best appropriate actions.
Thanks in advance,
Waran.
Dec 07 2018 06:52 AM
Hi Jamil,
I have tried everything and even inserted my sheet into your uploaded file but the count colours function only returns 0
Please help
Dec 07 2018 07:15 AM
Hi waran,
I checked your file. you have sent Conditional formatting for the same color (i.e red) on the same range. if you have multiple condition for the single color. then it needs to be put in one single formula, with OR function. the UDF does not work on multiple condition set for the same color with two different CF rule.
Dec 07 2018 07:17 AM
Jan 16 2019 07:09 AM
Hi Jamil, I believe I've done everything properly yet it still displays "#VALUE!" please help me.
Jan 16 2019 08:02 AM - edited Jan 16 2019 08:03 AM
Hi Julius835,
There are several issues with your workbook that is why UDF result in error.
I thought, I could explain it better by recording a video. Please see attached workbook and the video.
Jan 16 2019 12:09 PM
Jamil,
Thank you for your response. I have made the necessary changes and now my issue is if I try to count the cells only in certain rows I get an incorrect number.
Jan 29 2019 06:08 PM - edited Jan 29 2019 06:09 PM
Hi, Sir Jamil! Could you kindly help me how to make the UDF and formula you shared on counting the number of rows colored thru conditional formatting work? I copied and paste the UDF for this and followed the formula in your attached sample file, but I cannot get it right. The total is always zero. Here's the file I am working. Thank you very much!
Feb 06 2019 08:35 AM
Hi,
I have a problem too and I don't know how to fix it.
I would like to count colored cells for each line but it doesn't work.
Do you maybe know what's wrong in my excel file?
Feb 13 2019 01:51 PM
Feb 13 2019 01:58 PM
Hi Jo_Nolaj,
I looked at your workbook, the conditional formatting range and the range used in the UDF was not consistent. i changed it now and it works. plz see attached. please note that instead of 4 CF you could set them with OR function all in one. plz see the CF which i set. the only thing is that CF is replicated for each row using format painter.
Feb 13 2019 02:02 PM
Hi SC_8,
I checked your file. There CF range and the UDF used range in UDF were not the same. I modified the range by changing the CF for each row separately and therefore the UDF works now. plz see attached.
Feb 14 2019 04:18 AM
Thank you very much for helping me, but actually I have a probelm also with the file you edited.
When I open the file is absolutely perfect, but when I just click on the cells in which appears the number of colored cells per row (without making any kind of modification), it appears #VALOR and there is no way to make it work again. Same thing if I continue my work adding rows and I try to drag the column.
Thank you very much for help!
Feb 14 2019 04:20 AM
Thank you very much for helping me, but actually I have a probelm also with the file you edited.
When I open the file is absolutely perfect, but when I just click on the cells in which appears the number of colored cells per row (without making any kind of modification), it appears #VALUE and there is no way to make it work again. Same thing if I continue my work adding rows and I try to drag the column.
Thank you very much for help!
Feb 14 2019 09:46 AM
HI SC_8
plz check that your calculation method is not Manual. it should be automatic. also check that macro is enabled. if both of these fails then open the module and insert Application.Volatile just after the second line.
Feb 15 2019 12:06 AM
heyy all,
i need your helps about "How to count cells by colors with conditional formatting"
i tried ur datas but it didnt.
its so important for me.
Pls help me.
Feb 19 2019 11:32 AM