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 Evelina,

in your workbook, you have set the conditional formatting range differently than the range C5:AX5 you are using in the UD. Also your conditional formatting in column C is just for column C and then from D to the left is $D$6:$AX$6 and this range from column D to AX have not used formula to set up condition formatting. it has used the built in function of "Format only cells that contain" UDF only works if the conditional formatting rule is set using formula option. From what I see in your table, you could easily get the same result using built-in formula of SUMPRODUCT or SUMIFS. Again, your row 3 has n o data, so I would not know what would be the criteria for the formula.

[#3]

F M

If you examine in detail, your workbook conditional formatting has one rule and it is applied in the whole range of $A$3:$G$16 and when you use the UDF to count per each row then you are giving the UDF a range of single row $A3:$G3 while your conditional formatting range is $A$3:$G$16. Therefore, the mismatch between range of conditional formatting and the input range in the UDF. If you to make it work for per row, then you need to delete edit the conditional formatting and apply range should be the $A3:$G3 and then select $A3:$G3 and click the "Format Painter" and then select the each row separately to add the per row conditional formatting. Then your UDF per row will work.
Besides, you have locked the range to absolute reference in the UDF, so all of the rows refer to the row 3

Plz see attached workbook also with a quick video that I have recorded and uploaded here, You can watch the video to learn how you can fix the problem in your workbook.

[#4]

Hi Jamil, 

I created a test worksheet (highlighted yellow) in my workbook to test the UDF and it works, however it counts one too high for one condition. Could you look at this?

Also it appears that the UDF cannot handle my Conditional Formatting formula that I want to check. It uses the INDIRECT function to determine the cell that needs to be checked instead of a direct reference. I highlighted this worksheet green and put the color codes in the reference formatted cells. I don't think I can find a way to create a less complicated Conditional Formatting formula. If I want to rework the formula I need to move a lot of data and clutter the worksheets which I would like to avoid.

Could the UDF be modified to recognize INDIRECT functions?

Your dedication to this subject is outstanding btw 🙂

Mario

[#5]

Hello, Jamil

I have read the whole topic on the SumConditionColorCells forum and have not found the answer to my problem.

The file that I sent to you in the appendix contains conditional formatting. In places where there is formatting I want to put the number of employees needed to perform the task.

In the upper lines, add the number of employees needed on a given day. Unfortunately, the SumConditionColorCells function does not work for me in this range.

Please, if you have an idea how to solve it for help.

[#6]

Hi Mario,

the colors you selected for background are not the standard colors, so if you select from one of the standard colors, for example.  change the background color of Past due to red. then the UDF will result correct output.

plz see attached.

[#7]

Hi Zereba,

sorry, I do not have time inspect and add multiple condition formula in your gantt chart template. As you can see in your worksheet Arkusz1 the UDF works. So, making it work on the gantt chart is a matter of putting effort and time. And again, what you are trying to achieve, can be done with Excel built-in Formula similar to the post 204391 link https://techcommunity.microsoft.com/t5/Macros-and-VBA/sum-by-color-when-colors-are-set-by-conditiona...

[#8]

THX Jamil

[#9]

You are welcome Zareba.

[#10]

Hi Jamil,

I have a large database (In excess of 200Columns & 8000Rows) which needs to be sorted out by cell color and other multiple criteria. I have tried out multiple things but failed.

I'm basically looking for type of SUMIFS function with ColorIndex as Criteria.

A snap shot is attached.

[#11]

null null,

it does not work because you perhaps did not read the rule on how to use this UDF. please see my post here https://techcommunity.microsoft.com/t5/Macros-and-VBA/sum-by-color-when-colors-are-set-by-conditiona...

""UDF will only work, if your rules of conditional formatting is created using conditional formatting rules with formula aka (use a formula to determine which cells to format) ""

[#12]

Hi Jamil,

I had read the all the thread post, But the problem is my worksheet is too large to apply conditional formatting and excel crashes upon conditional formatting.

Further, Color coding as seen in each cell is coming from series of various conditions in other spreadsheet. (i.e For each cell in Qty, There are 4-5 activities to be completed each activity having unique color, If particular activity is completed, It will return only "ColorIndex" in current sheet)

Hence, I need to sum the fields using "ColorIndex" and other criteria's.

[#13]

If you are looking for UDF to sum based on index color and not conditional formatted color. then it is even simpler. 

you can take the UDF from link here

[#14]

Hi Jamil,

The said UDF is not suitable to my problem as it cannot sum/count with multiple criteria.

Further i have checked multiple forums for sumif by Cell Color but non are matching my problem.

Extended version of sample result required is attached.

[#15]

Hi null null,

what you are trying to achieve can be done with a very complicated UDF but will be very CPU hungry.

If your colors marks are based on some hardcorded condition which is not from conditional formatting, then use those conditions inside the SUMIFs or SUMPRODUCT and if your colors are based on conditional formatting rules, then use those rules you used in the conditional formatting inside the SUMIFs or SUMPRODUCT formula.

for example, from your previous file, you have condition of  if cell values is between 

=0.2791 and =0.3062 then it is red.

so you can use the same logic inside the formula for example like this =-SUMIFS(E3:K35,E3:K35,">=0.2791",E3:K35,"<=0.3062")

[#16]

Hi Jamil,

Actual what you are same is Correct, I'm already running VBA code in 10 parts to save CPU Memory.
The second logic with example makes some sense, I will try the same in greater depth of details.
I will let you know the result. Thanks in Advance.

[#17]

Hi Jamil,

Your suggestion worked perfectly.. Thanks at lot..

[#18]

You are welcome. glad it worked for you.

[#19]

Hi jamil,

I'm little lost & confused and trying to make my code bit short.

In multiple if, Elseifs statements in VBA, I need to use array, But not able to debug it.

Excel version of same is attached, Can you help me with vba code..

[#20]

Hi Jamil,

Late reply but the colors were indeed the issue for the Test Sheet. For my actual data the UDF doesn't work, even with standard colors. I think it has something to do with the ConvertFormula function in the UDF. My Conditional Formatting uses INDIRECT to determine the cell reference and not a direct cell reference type.

Can this be an issue? And can this be reworked? Workbook hasn't changed.

Cheers, Mario

[#21]

hi, tnx for your file. is it possible to count color of conditional formatting cells in excel with vba only for those cells which are only numberic?