SOLVED

sum by color when colors are set by conditional formatting

Copper Contributor

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

 

304 Replies
Hi Z Z,

I have already provided you the solution based on your template and you have confirmed that it worked.
link here https://techcommunity.microsoft.com/t5/Excel/sum-by-color-when-colors-are-set-by-conditional-formatt...

Your latest file have several issues; CF used without use of formula and formulas outside the CF range and many named ranges with broken links that and requires time to debug and analyse, the time which I do not have. As I only contribute to the extend possible and I cannot dive deep and build solutions.

You can post new questions as a new thread on this forum, perhaps other Experts might be able to help you. Or if you need an instant solution, you can hire a Dev for an hour or so, to make this work for you.

Thanks for your understanding.

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.

 

Glad to hear that you have already figured it out. Thanks for the feedback.

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. 

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

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.

Hi
If you read my earlier posts. you will see that I mentioned that for the UDF to work. CF needs to be creating using formula. I checked your CFs are not creating using Formula and that is why it does not work. If you change your CFs to be based on formula then it will work.

Hi Jamil, I believe I've done everything properly yet it still displays "#VALUE!" please help me.

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.

 

 

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.

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!

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?

Hi Julius835,

As you can see in my earlier post. The range inside UDF and the range of CF both must be the same, otherwise the UDF does not count. Perhaps It would be easier if you use SUMPRODUCT function to count conditionally based on multiple criteria.

Put a helper column next to column E. you can hide that if you want. put real dates in them and then use something like =SUMPRODUCT(--(MONTH(F:F)=MONTH(TODAY())),--(YEAR(F:F)=YEAR(TODAY())))

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. 

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.

 

 

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!

 

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!

 

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.

 

asdas.jpg

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.

 

 

@Imhuman

Please read my earlier post, the conditional formatting needs to be set using formula.
in your file the CF is not set using formula, that is why it does not work. if you set CF with formula then UDF will work.