sum by color when colors are set by conditional formatting

[matt nipper]

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

[#2]

Hi Jamil
Thanks for the solution, is there a limit in setting a range because when i am trying to set range with more than 7 columns its saying #value.

can you please help

[#3]

Hello Jamil I have looked at this and downloaded your spreadsheet but  the formula in J2 and J3 doesn't count anything it just says #Value! am I missing something here because it doesnt seem to work at all. @Jamil Mohammad 

[#4]

@Jamil Mohammad I am also getting the No Color error and desperately need your help.

I have tried to troubleshoot but have not had any success.

How can I attach the file for you to view?  It keeps telling me "file type is not supported"

[#5]

Hi Jamil
First of all, thank you for this code it has been massively helpful to me. I have an issue I am hoping that you can help me to resolve. I got the code working except for this part:

dbw = CFCELL.FormatConditions(CF1).Formula1
dbw = Application.ConvertFormula(dbw, xlA1, xlR1C1)
dbw = Application.ConvertFormula(dbw, xlR1C1, xlA1, , ActiveCell.Resize(CellsRange.Rows.Count, CellsRange.Columns.Count).Cells(CF3 + 1))

My understanding is here is where the code is counting the change in colour and updating the 'dbw' variable accordingly. I have used your example version and it works perfectly. When I run it in my workbook it doesn't work once the loop is finished I am always returned 0. If I edit the code I can get it to count all the cells that have a colour but I then have the issue that it won't differentiate between the different colours and will return me the number of all the coloured cells. Please, can you advise why this section of the code may not be working for me?

[#6]

@Jamil Mohammad 

Hi Jamil,

Good day, i've been trying to adapt my document to all the ones you already shared to count the number of cells colored by a conditional formatting but no succeed.

On my file i'd like to count the number of cells in red, yellow and green.

Could you help me with it?

if you allow me i could send it directly to you by message as i cant upload any document into this message.