12-20-2016
01:16 PM
- last edited on
07-25-2018
09:33 AM
by
TechCommunityAP
12-20-2016
01:16 PM
- last edited on
07-25-2018
09:33 AM
by
TechCommunityAP
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
12-21-2016 01:44 AM
Hi Matt,
may be using the SUMIF or SUMIFS functions could help. You can define as criteria the same criteria you defined for the conditional formats. And, you don't need macros then. If you prefer macros, please have a look on this article from Ablebits. There is a section for adding values on conditional formats.
Best,
Mourad
01-08-2017 03:57 AM
@Deleted's suggestion of an extra helper column for a sumif is by far the safest and most easily understood option.
01-08-2017 08:26 AM
Thank you for the response Mourad. sorry i didn't see your post until Wyn responded.
I will try the sumifs function. I hadn't thought about burying the conditional formatting formula in the sumifs formula. Maybe that will work.
01-19-2017 01:32 PM - edited 03-27-2018 08:07 AM
SolutionHi matt nipper,
since you mentioned that you "have done an exhaustive search online" i have come up with a solution for you, although it has two limitations A) it 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) and B) the UDF will only work if sum range is more than one cell another word, it will not sum a single cell, as well as the conditional formatted range is more than one cell.
the example file, you can download it from here. I could not upload it here, as it is a Excel Macro-enabled Workbook that contains the UDF
Function SumConditionColorCells(CellsRange As Range, ColorRng As Range) Dim Bambo As Boolean Dim dbw As String Dim CFCELL As Range Dim CF1 As Single Dim CF2 As Double Dim CF3 As Long Bambo = False For CF1 = 1 To CellsRange.FormatConditions.Count If CellsRange.FormatConditions(CF1).Interior.ColorIndex = ColorRng.Interior.ColorIndex Then Bambo = True Exit For End If Next CF1 CF2 = 0 CF3 = 0 If Bambo = True Then For Each CFCELL In CellsRange 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)) If Evaluate(dbw) = True Then CF2 = CF2 + CFCELL.Value CF3 = CF3 + 1 Next CFCELL Else SumConditionColorCells = "NO-COLOR" Exit Function End If SumConditionColorCells = CF2 End Function

in the attached example file you can see that from A3:G16 cells are formatted using Conditional Formatting.
User Defined Function (UDF) is placed in cells J2 & J3 and cells I2 and I3 are the criteria color used as reference inside the UDF in J2 and J3.
I hope this helps you.
Edit: uploaded file and updated the code to the correct one.
01-22-2017 03:32 PM
Hello,
take a look at this question in the Microsoft Community formerly known as the "Answers" Q&A forum. (I know, the "Community" site name is confusing to have on two different sites).
Make sure to look at all the replies to see how to get from conditional formatting to a Sumifs or Countifs formula.
11-07-2017 04:32 AM
Hi Jamil,
I came across your solution to a similar query i have, and tried applying it to my data.
It almost works however the summed total is slightly off the total when i manually sum the data. it seems like it may be a rounding issue....do you have any suggestions on how to resolve?
Thanks in advance,
Micheal
11-07-2017 04:56 AM
Hi Michael,
there is no rounding issue. my suggestion is to revisit the colors and try to run the test on a smaller data set and see how it goes. if you can provide a sample dummy example. i can take a look.
11-07-2017 05:16 AM
Hi Jamil,
Thanks for the quick response. It definitely appears to be a rounding issue. I looked at a smaller set of data and ran some tests on it.
The data I tested was the following 6 values - 45.18, 45.26, 45.21, 45.39, 45.24, 45.41
The sum value should be 271.69, however the function is returning a sum of 270.00 (which is each value rounded to its nearest whole number - 45*6)
I then changed the first value to 45.68, at which point the function returned a sum of 271.00 (rounding the first value up to 46, and continuing to round the remaining values down to 45)
Unfortunately I'm not in a position to attach any samples, but if there is any reason that you think this issue is occurring then please let me know.
Thanks in advance.
Micheal
11-08-2017 03:29 AM
Hi Michael,
Ok. Can you please replace the previous UDF with this one attached below. This should fix the problem.
Please let me know how it goes.
Function SumConditionColorCells(CellsRange As Range, ColorRng As Range) Dim Bambo As Boolean Dim dbw As String Dim CFCELL As Range Dim CF1 As Single Dim CF2 As Double Dim CF3 As Long Bambo = False For CF1 = 1 To CellsRange.FormatConditions.Count If CellsRange.FormatConditions(CF1).Interior.ColorIndex = ColorRng.Interior.ColorIndex Then Bambo = True Exit For End If Next CF1 CF2 = 0 CF3 = 0 If Bambo = True Then For Each CFCELL In CellsRange 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)) If Evaluate(dbw) = True Then CF2 = CF2 + CFCELL.Value CF3 = CF3 + 1 Next CFCELL Else SumConditionColorCells = "NO-COLOR" Exit Function End If SumConditionColorCells = CF2 End Function
11-08-2017 03:35 AM
Hi Jamil,
Thanks again for the response.
I've just tried your amended code and it works perfectly. Your help on this is very much appreciated!
Best Regards,
Micheal
11-12-2017 05:58 AM
11-27-2017 10:22 PM
02-25-2018 01:53 AM
Is it possible for cell count instead of sum. Please share the VBA code if possible.
02-26-2018 07:18 AM
Yes, it is possible and here it goes.
Function COUNTConditionColorCells(CellsRange As Range, ColorRng As Range) Dim Bambo As Boolean Dim dbw As String Dim CFCELL As Range Dim CF1 As Single Dim CF2 As Double Dim CF3 As Long Bambo = False For CF1 = 1 To CellsRange.FormatConditions.Count If CellsRange.FormatConditions(CF1).Interior.ColorIndex = ColorRng.Interior.ColorIndex Then Bambo = True Exit For End If Next CF1 CF2 = 0 CF3 = 0 If Bambo = True Then For Each CFCELL In CellsRange 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)) If Evaluate(dbw) = True Then CF2 = CF2 + 1 CF3 = CF3 + 1 Next CFCELL Else COUNTConditionColorCells = "NO-COLOR" Exit Function End If COUNTConditionColorCells = CF2 End Function
02-26-2018 07:23 AM
Hi t.aerdts,
I think that can be achieved using Formulas with the referred UDF. If you post a sample dummy data here and I will have a look in the weekend.
02-26-2018 12:42 PM
Hi Jamil,
herewith the sample file.
appreciate your input.
I put some comment in the file.
KR T.aerdts
03-02-2018 04:46 AM
Hi,
I just noticed that the zip file you uploaded is blank.
besides, this thread is related to conditional formatting, so i suggest you open a new question with uploading the sample file with it.
03-10-2018 12:38 AM
Hi Jamil,
attached the file with content.
It would be great if the cells can be summed when the color is set by conditional formatting.
inside the excel file I insert some remarks and tekst for declaration.
Tillo
03-10-2018 05:40 AM
Hi Tillo,
I looked at your file. you are using a incorrect UDF to sum cells based on conditional formatting.
I cannot fill your template for you. you need to copy the below UDF put it in a module in your workbook.
then use it in your workbook =SumConditionColorCells(CellsRange As Range, ColorRng As Range)
like this =SumConditionColorCells(O$9:O$40,$N6)
I found another problem with your workbook. the color used in the conditional formatting and the cells referenced as the color criteria e.g. $N6 are not the same color. unless they are the exact same color, the UDF will not work. I have attached a workbook that shows how the UDF works. so you need to adapt that in your own workbook.
Sorry, I cannot help further on this.
Function SumConditionColorCells(CellsRange As Range, ColorRng As Range) Dim Bambo As Boolean Dim dbw As String Dim CFCELL As Range Dim CF1 As Single Dim CF2 As Double Dim CF3 As Long Bambo = False For CF1 = 1 To CellsRange.FormatConditions.Count If CellsRange.FormatConditions(CF1).Interior.ColorIndex = ColorRng.Interior.ColorIndex Then Bambo = True Exit For End If Next CF1 CF2 = 0 CF3 = 0 If Bambo = True Then For Each CFCELL In CellsRange 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)) If Evaluate(dbw) = True Then CF2 = CF2 + CFCELL.Value CF3 = CF3 + 1 Next CFCELL Else SumConditionColorCells = "NO-COLOR" Exit Function End If SumConditionColorCells = CF2 End Function
03-10-2018 09:53 AM
Hello!
Do you have a sample excel file where you use the count by conditional format?
I am having problems using it.
Thanks!
03-11-2018 09:22 AM
Hello Bethany,
Yes, I have attached it here.
03-15-2018 02:15 PM
03-15-2018 02:54 PM
On your worksheet all the colors work just fine. But on mines, for some reason only 1 out of 3 colors i'm using works, the light blue. Those columns that are highlighted are conditionally formatted using a formula.
I've made 3 sample formula for each of the colors.
03-19-2018 03:03 AM
03-19-2018 09:58 AM
When I use the code, it shows #VALUE!. This is happening once the condition changes.
I am using Excel 2016 with Windows 10 Pro.
03-19-2018 11:17 AM
03-20-2018 07:42 PM
Hi! I've also been getting the #Value error. I copied and pasted the Count version of the VBA. Screen shot attached let me know if you need more. I entered the RGB color codes so they are exact.
03-21-2018 11:16 PM
Good evening Jamil,
Do you happen to have a version of this UDF which would sum cells with a given font color rather than an interior/background color? I have tried changing the word "Interior" to "Font" in the UDF, however when I run the formula on my desired cell, it simply shows "NO-COLOR" rather than adding the cells with the desired conditionally formatted text color. Any help would be appreciated.
Regards,
-Steve
03-22-2018 05:29 AM - edited 03-22-2018 05:34 AM
Hi Steve,
If you mean the font color set by conditional formatting. then you can change the line
From
If CellsRange.FormatConditions(CF1).Interior.ColorIndex = ColorRng.Interior.ColorIndex Then
To
If CellsRange.FormatConditions(CF1).Font.Color = ColorRng.Font.Color Then
Please see it in the attached sample workbook.
03-22-2018 05:36 AM - edited 03-22-2018 05:47 AM
Sometimes this happens, if you have an external Add-in or there are some codes in the PERSONAL.XLSB workbook.
click on the cell that has #Value error and then press F2 to go to Edit mode and then enter again. if you see the VALUE error disappear that means that the error is caused by some external add-in or your personal.xlsb workbook that is in the start up folder.
If you can share a dummy example file, I can take a look at it.
03-22-2018 07:35 PM
Thanks Jamil, this gets me very close. However, I suppose what I really need is for this VBA to count the number of cells within the selected range if they are a certain color, not sum the values within the cells, as the values within the selected cell range are text, not numbers. What would I change in the VBA to accommodate this?
Regards,
-Steve
03-23-2018 03:28 AM
Hi Steve,
I have modified the code and attached is the version that counts instead of sum.
03-23-2018 07:44 PM
Jamil,
This modified code does not seem to be working. I am getting a #VALUE! error in my target cell that should be displaying the count of cells with colored text in my selected cell range. Would there be any part of the code I would need to change so that it will work with my cell range containing text? It doesn't seem to matter whether I choose General or Text for the cell format, the formula still results in a #VALUE! error.
03-26-2018 10:03 AM
03-26-2018 10:37 AM
Still getting the same error, thank you for taking a look, it's attached.
03-27-2018 07:56 AM - edited 03-27-2018 08:14 AM
Hi Steve,
I have attached the workbook that shows the count of conditional formatted area that has text values in them. it works. I have no idea why it does not work in your workbook. Perhaps, your workbook conditional format rules are not set by formulas. Please read the limitation of the UDF in my post
Alternatively, If it does not work then I suggest that rather than Counting or Summing based on the outcome of the conditional formatting, you use the same criteria as exists in the conditional formatting to Count or Sum the cells using SUMIFS/COUNTIFS or SUMPRODUCT
03-27-2018 08:19 AM
03-31-2018 10:12 PM
Jamil,
I am not sure why this would not be working either, as the conditional formatting is being determined by formulas. Perhaps this is not working due to an add-in (or lack thereof), or perhaps it has to do with the cells with conditional formatting having drop-down lists? I have attached a draft of my workbook for your reference. Would you be able to take a look and try to see why the formula based on your UDF won't calculate?
Thanks for your help!
04-11-2018 10:21 AM
Hey Jamil,
I used the count condition to calculate the cells based on conditional formatting and for some reason at first it showed No COUNT and now it shows value error.
I am attaching the file. Can you please look at it and provide your feedback on how to fix the error.
Note: - I changed the name from .xlsm to .xls to upload it
04-11-2018 10:28 AM
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.
04-11-2018 11:13 AM
Thank you Jamil,
Is there any solution for that?
Appreciate your help.
04-21-2018 02:51 AM
Hey Jamil,
the code is not working on my file, could you please help me.
04-21-2018 08:26 PM
Hi 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..
04-24-2018 08:45 AM
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.
04-24-2018 08:49 AM
04-24-2018 09:29 PM
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..
04-25-2018 12:05 AM
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.
04-25-2018 03:27 AM
05-09-2018 01:02 PM
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?