- last edited on
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
06-16-2018 11:59 AM
06-16-2018 12:31 PM - edited 06-16-2018 12:50 PM
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.
06-23-2018 02:44 PM
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 :)
06-25-2018 12:54 AM
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.
06-25-2018 10:01 AM
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.
06-25-2018 10:05 AM
06-26-2018 11:09 PM
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.
06-27-2018 02:05 AM
06-27-2018 02:29 AM
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.
06-27-2018 06:15 AM
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.
06-27-2018 06:48 AM - edited 06-27-2018 06:49 AM
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")
06-27-2018 07:06 AM
06-29-2018 01:54 AM
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..
07-06-2018 10:55 AM
07-11-2018 12:40 PM
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?