Forum Discussion
sum by color when colors are set by conditional formatting
Hi 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.
Jamil First thank you for this effort. Second I try the Function you gave to count the formatted colored cells, it is working only for one row and I need to pull down it always giving the reading of first row. Please Can you help with this to let this Function working as I pull down to the next rows.
There I have shown how you can do it per row. file is attached there and also the GIF animated recorded video.
Jamil Hi Jamil, thank you for this wonderful formula, it's my first UDF and was very easy to set up. I have the same issue as others regarding the fact that the formula only works for the first column and then every column after is returning the same count as my first column - however this link you're referencing appears to be dead. I'm hoping you can help me by resharing the link to how to resolve this issue and/or video. Here's the formula returning the same answer for every section (I counted and it should not be 9 for every single month). Thank you!
Hi, albeit your question is not related to the topic in this thread, I will try to give you an answer.
there is an easy way to check if a range is sorted sorted. It is quite easy, even if you have a single blank in your data. Examples https://exceloffthegrid.com/excel-formula-to-check-if-a-list-is-sorted/
However, with the data you presented, it is much more complicated as the data is scattered with multiple blanks in between them and sometimes there is only a single value or duplicate values which makes it difficult to find a pattern for the formula.
Perhaps you can work out using some helper columns to clean the data before running the logical test.
Jamil,
Thanks for helping, and sometimes doing it almost at light speed. You have helped me in the past.
I have a similar problem, but instead of counting, I just need to show on the screen by conditional formatting which numbers match the criteria.
How can I use conditional formatting to highlight all the numbers in a row if they go in ascending order across columns, and skipping the blank cells.
Also, which formula do I use to show next to the beginning (or end, or someplace) of the row a "YES" if the criteria is met?
I attached an example. I repeated the tables. The fist table at the top is where the CF or formula is needed. The bottom part, the repetition is where I illustrate what I need, how it needs to look.
Thank you so much for helping.
