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

Suril,

 

you have used the UDF as COUNTIFs function which does not work.  It accept one range and one criteria, it does not work when multiple criteria and ranges are given in the UDF argument.

 

 

Thank you Jamil, 

 

Is there any solution for that? 

 

Appreciate your help. 

Hey Jamil,

 

the code is not working on my file, could you please help me.

colored cell.PNGcount cell.PNGHi there,

I'm using your code to count my conditional formatting colored cell, but the cell cannot be counted. The code maybe cannot read my colored data cell. Could you please help me? It shows no color when I'm trying to use the CountConditionColorCells formula.

 

Tq..

Hi Fenfen,

 

you have given the range of cell into the UDF range arguments cells that do not have conditional formatting applied to them.  

 

UDF will throw error if range of cell given as input that do not have conditional formatting rule applied to it.

 

so to make your workbook work. you could achieve the result by using a combined addition 

=COUNTConditionColorCells(B3:I3,J2)+COUNTConditionColorCells(B8:I8,J2)+COUNTConditionColorCells(B13:I13,J2)++COUNTConditionColorCells(B18:I18,J2)

see attached file.

Hi ogyramly,

Please read the earlier posts through this thread and try the suggestion given and the limitations of the UDF, i suspect that one of the issues already known could have caused the UDF to return no color.

Hi, thanks for your respond.

Does the formula of conditional formatting for format cell that contain can be applied using this code??

I cannot link my problem with the others as they might using different conditional formatting formula..

H


@Jamil Mohammad wrote:

Hi Fenfen,

 

you have given the range of cell into the UDF range arguments cells that do not have conditional formatting applied to them.  

 

UDF will throw error if range of cell given as input that do not have conditional formatting rule applied to it.

 

so to make your workbook work. you could achieve the result by using a combined addition 

=COUNTConditionColorCells(B3:I3,J2)+COUNTConditionColorCells(B8:I8,J2)+COUNTConditionColorCells(B13:I13,J2)++COUNTConditionColorCells(B18:I18,J2)

see attached file.


Hi Jamil,

 

I have tried, but its not working. cause my the color will be change anytime. please see sample file.

Hi Fenfen,

It is because the conditional formatting formula of Vlookup uses the same range that is used in the formula on the cells that produce the numeric results, so it is kinda create circular reference in a nontraditional way. The UDF is not designed to handle this sort of complex scenario. the UDF works on the simple conditional formatting that would use a formula that evaluate cells that has either simple values or if they have formulas, they are not using the same range as the formula used in the conditional formatting.

I suggest you use the other methods described in the earlier posts, like combination of SUMPRODCUCT with COUNTIFS

Hi, this is exactly what I'm looking for but I just need count not sum.  Is it easy to re-produce this as a countif?

Hi Jamil,

 

How do I use the UDF to work on all the excel files by default, not just a specific one? Is there a way to set it up under personal macro file?

Michael,

 

please see attached example for COUNT as requested.

 

How to sum conditionally formatted cells based on adjacent cell

Hi Jamil,

 

I tried to use the UDF you shared to sum up conditionally formatted cells based on adjacent cell. The conditional formatting is done using SEARCH formula, basically I am trying to sum up all the sales values for which the adjacent cell (text) contains certain strings. I saw your post related to more complex formulas like VLOOKUP with which the UDF doesn't work, not sure whether SEARCH falls under the same. Appreciated if you can look into it and let me know in case something can be done so the UDF would work in my case as well. Attached the example file.

Andrea,

 

I think you can easily achieve the result you need without over-killing it with conditional formatting UDF.

 

in I5 put Worldwide and in I6 put USA and in the adjacent cell  in J5 put this formula and copy down =SUMPRODUCT(--(ISNUMBER(SEARCH(I5,$E$5:$E$12))),$F$5:$F$12)

 

this will sum based on the condition given and the good part about this is that you do not need to save your excel file with macro enabled option. you can get your results by using built-in Excel functions which is great.

 

please see attached workbook where I embedded the formula in it.

Thanks Jamil, this is super! exactly what I wanted. 

You are welcome Andreea. Thanks for the feedback.

Hi Jamil,

 

I tried to work the count cells by color to work within my workbook, but one sheet isn't working for some reason. I have attached a test version of the document showing the miscounted number. I have 5 red highlights, and it says 2.

 

Thanks,

Hi Carl,

 

 

Looking at your workbook, there is a mismatch between (applied range to) in the conditional formatting and the UDF.

 

If you look at your conditional formatting rules, the red color background rule is applied into three separate noncontinuous range $A$1:$A$54  , range $D$1:$M$54  and range   $B$1:$C$54

while your User Defined function range input is the whole range A1:M54.

The UDF looks up for the match for ranges used in the conditional formatting and the input range inside the function and if there is no match, it will not output correct result.

 

So, if you want to work with the UDF you need to use combination of ranges together in UDF exactly as per the conditional formatting range.  so if you put this =COUNTConditionColorCells($A$1:$A$54,O2)+COUNTConditionColorCells($D$1:$M$54,O2)+COUNTConditionColorCells($B$1:$C$54,O2)

 

it will return 5 which is correct.

 

but if you do not want to use the UDF, then you can acheive the same result by using Excel built-in Functions  like this 

=SUMPRODUCT(--($A$1:$A$54<=(TODAY()-730)))+SUMPRODUCT(--($D$1:$M$54
<=(TODAY()-730)))+SUMPRODUCT(--($B$1:$C$54<=TODAY()-365))

 

The above formula using built-in Excel function will result the same, it returns 5.

 

Hope it helps.

Hi Jamil,

 

I'm trying to count how many cells that are green marked but only gets 0 as the answer even though it should be one. What am I doing wrong? I'm using the vba for counting that you attached earlier in the thread and also conditional formatting.

Hi, I am trying to get a count of each row in your attached example, but when I am passing the row information, it only works correctly for the first row and then the others give the same result. Any way to get this done?