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.
As I have rightly guessed. your conditional formatting rules were overlapping each other.
colors were all applied in the same column, while conditional rules were from different columns.
so, if you apply the conditional formatting columns in the columns were they are relevant then calculation will be correct. plz see example file.
also alternatively, you can also get the same result by using SUMPRODUCT function without the need for the UDF. please see example file of sumproduct.
Hi Jamil ,
Thank you for the response, however, neither spreadsheets work as planned.
1) The number of 'jobs ongoing' (red) stays the same - which it shouldn't do. When your put a date in the column for inspected, the red should go down one, and orange up one. Then again, when you put a date in date completed, green should go up 1 and orange down 1. As it stands, it looks like X amount of jobs are ongoing (not inspected) when in fact Y jobs have been inspected and should be taken off the red colour.
If this makes sense?
The job number column must also retain the colour.
Hi Jamil,
I have been reading your posts and you are able to solve all issues regarding the count/sum of conditional colored formatting cells.
I have tried using the UDF you mentioned for the count but I am not getting any result.
I have attached my file here with the desired output. Can you please look into this? I have looked at multiple solution and nothing is working so far.
Your help is greatly appreciated!
- Dave, You are welcome. I am glad I was able to help.
thanks for the feedback.
take care, cheers, Jamil - Jamil, thank you so much! I've just had a quick review and your solution works wonderfully for my needs and has helped tremendously. Again, I truly appreciate your patience and guidance, especially with such a novice as myself. Take care and I hope you are well. Cheers, Dave
Hi GSMCONNECT
you misunderstood on the use of the SUMPRODUCT formula. it was not meant to be used inside conditional formatting. it was meant to replace the UDF. I have now placed three formulas in cells B51, C51, and D51.
the formulas work just fine, so you do not need to use UDF SumConditionColorCellsplz see attached file with the formulas.
Hi Jamil,
thank you very much for your reply
I started excel in safe mode and opened the file but still get a # VALUE error.
I also pasted the sumproduct formula you posted but still get a # VALUE error.
then I opened the file in excel 2007 which was installed in English but still get a # VALUE error.
the fact is I need a double condition in the CF of my excelfile.
can you please help?
thx
- please share a sample spreadsheet.
- Unfortunately, I have still not been able to sort out my count issue; if anyone is able to review my sample spreadsheet and provide some guidance it would be appreciated. Thanks, Dave
- HI GSMCONNECT, Yes, it could be the language issue, but I am not sure.
in B51 you can use a formula and completely disregard the UDF. a formula to get that result is =SUMPRODUCT(($P$9:$P$18)*(ISNUMBER($P$9:$P$18))*(LEN($P$9:$P$18)=1))
and in Dutch =SOMPRODUCT(($P$9:$P$18)*(ISGETAL($P$9:$P$18))*(LENGTE($P$9:$P$18)=1)) - Hi Jamil,
thank you for your reply.
I sent the file to a friend of mine and he gets the # VALUE error also.
could this be a language problem? I'm from Belgium and our excel is in dutch, not in English.
thx - Hi GSMCONNECT, Mould Template1.xlsm is not my file, if you downloaded from one of the answers in the thread, it could be my reply to one of the questions in the thread. so, I did not ever create such file. However, I quickly looked at the file you shared. I do not get any #VALUE error. Perhaps, it is some Add-In or any other file in your excel start up folder that interferes with the UDF. Try to disable them and then the error may disappear.
- Hi bobbyfisher, I looked at your file and the reasony ou get the VALUE error, because of the two reasons. A) the conditional formats are not set using formulas B) the range inside UDF and the conditional formatted range are not consistent. If you look at my earlier posts, I have mentioned the three limitations of the UDF. so, unless those conditions are satisfied, the error will not go away.
Hi Jamil,
I downloaded "Mould Template1.xlsm" on one of your posts. For a reason I don't understand I get a "# VALUE" error in B51it has something to do with the AND function in the CF.
when I don't use the AND function in CF it works.
the fact is I need the AND function in the CF of my sheet
Can you please help?
thx- Hi
I will take a look later today. It is too early in the morning here. Jamil,
Feel bad for bothering you with what I think is going to be a simple fix, but for the life of me I can't figure what I'm doing wrong here.
In the attached, which I've downloaded from one of your posts, I have a series of conditionally formatted cells (for instance AJ9:AU9) which when TRUE one or more cells will turn purple.
I'm trying to use your formula in cell AX9, and the minute I try to change the RANGE to AJ9:AU9, checking against color purple in cell AW9, I get "#VALUE".
If you don't mind, why does it error out?
I appreciate it, and hope you have a good weekend. Stay safe. 😉
Jamil,
Feel bad for bothering you with what I think is going to be a simple fix, but for the life of me I can't figure what I'm doing wrong here.
In the attached, which I've downloaded from one of your posts, I have a series of conditionally formatted cells (for instance AJ9:AU9) which when TRUE one or more cells will turn purple.
I'm trying to use your formula in cell AX9, and the minute I try to change the RANGE to AJ9:AU9, checking against color purple in cell AW9, I get "#VALUE".
If you don't mind, why does it error out?
I appreciate it, and hope you have a good weekend. Stay safe. 😉
Hi Jamil; I'm a complete novice in Excel so please be patience 🙂
I have a spreadsheet that manages a groups lottery numbers; I enter the actual drawn numbers into their respective cells, and any matching numbers from the group members chosen numbers are highlighted by Conditional Formatting, i.e., =COUNTIF($F$18:$L$28,F32)>=1
Fortunately, I found this thread and have tried to incorporate your “COUNTConditionColorCells” VBA function? Unsure of correct terminology. Additionally, I have downloaded and reviewed your example “COUNTBYCOLOR.xls” and have tried to use it for my needs without success. I have also read through the many conversations but found it to be confusing me more than resolving the issue, i.e., I tried to implement several similar situations/examples, but this only muddied the waters so to speak.
Unsure, but suspect my issue “may” be in my use of the “=COUNTIF($F$18:$L$28,F32)>=1” formula for conditional formatting. It highlights well, but regardless of what I try, the best I can achieve is a return of “NO COLOR”, where I require a count of the highlighted coloured cells in the row.
The attached spreadsheet is for your review and hopeful resolution.
Thank you and I look forward to your guidance.
Dave
Glad to hear that you found a solution. If you read my earlier posts in this same thread, you will see that I have offered solutions to some users using similar method you found using SUMPRODUCT. I always mentioned in this thread that these UDFs have limitations and can serve as suboptimal solution, while there is always better ways to get the same result using formulas, but not many people can manage to write those complex formulas, so they go with the suboptimal solution of UDF by giving the ranges the modification needed to make it work.
Thanks for replying.
About what you said that:
"your CF applied range is applied vertically, while in the UDF the counting rate is horizontal range. This will not work, unless both CF range and the range used in the UDF are the same dimension."
"This will not work" is not accurate, fortunately. See attached file. A solution was provided already a few days ago elsewhere and I have attached it here for everyone's benefits. The solution only uses a formula, no VBA needed, although I'm sure it can be done that way too. This solution allows the CF going vertically and the counting/adding horizontally, just the the way I have it and is needed.
If you study the file, you will see that because of the type of data that is being evaluated, it must be laid out in a certain way and the conditional formatting must be applied per column, vertically, and independently of the other columns. Then, the counting of those CF must be done horizontally, per row, to be able to pinpoint the higher match. Of course the data can be laid out in many other ways, which will give a different perspective, but the one we need is this one at the moment.
What's the point of using Excel if we have to maneuver the data according to the limitation of Excel instead of the other way around?
Thanks. Stay safe.
Hi, if you read my earlier posts in the same thread. you will see that many users made the mistake of not using consistent range within the UDF argument and the CF applied range. Same has happened in the file you shared, if you look your CF applied range is applied vertically, while in the UDF the counting rate is horizontal range. This will not work, unless both CF range and the range used in the UDF are the same dimension. Hope it helps.
Hello, I have a similar situation but I can't make it work.
I need to count how many cells in a row range have green CF, and then it is also needed to sum the values of those cells found. Obviously, the result of counting will be in one cell and the summing will be in another.
The excising (and working) conditional formatting is set to find the largest number per column, for obvious reasons. Within the table, the data has been divided into two ranges/groups, but it is still one table. The data has a Table Style.
In the attached file, the corresponding calculation area (which I can't solve) per row is to the right of the table, in columns P, Q, R, for the first range (Group 1), and T, U, V for the second range (Group 2).
I wrote some explanation and a sample of what is needed inside the file.
Thanks you so much in advance.
