Dec 20 2016
01:16 PM
- last edited on
Jul 25 2018
09:33 AM
by
TechCommunityAP
Dec 20 2016
01:16 PM
- last edited on
Jul 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
Dec 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
Jan 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.
Jan 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.
Jan 19 2017 01:32 PM - edited Mar 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.
Jan 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.
Nov 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
Nov 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.
Nov 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
Nov 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
Nov 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
Nov 12 2017 05:58 AM
Nov 27 2017 10:22 PM
Feb 25 2018 01:53 AM
Is it possible for cell count instead of sum. Please share the VBA code if possible.
Feb 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
Feb 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.
Feb 26 2018 12:42 PM
Hi Jamil,
herewith the sample file.
appreciate your input.
I put some comment in the file.
KR T.aerdts
Mar 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.
Mar 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
Mar 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
Jan 19 2017 01:32 PM - edited Mar 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.