SOLVED
Home

sum by color when colors are set by conditional formatting

Hi Jamil,

 

How do I use the UDF to work on all the excel files by default, not just a specific one? Is there a way to set it up under personal macro file?

Michael,

 

please see attached example for COUNT as requested.

 

How to sum conditionally formatted cells based on adjacent cell

Hi Jamil,

 

I tried to use the UDF you shared to sum up conditionally formatted cells based on adjacent cell. The conditional formatting is done using SEARCH formula, basically I am trying to sum up all the sales values for which the adjacent cell (text) contains certain strings. I saw your post related to more complex formulas like VLOOKUP with which the UDF doesn't work, not sure whether SEARCH falls under the same. Appreciated if you can look into it and let me know in case something can be done so the UDF would work in my case as well. Attached the example file.

Andrea,

 

I think you can easily achieve the result you need without over-killing it with conditional formatting UDF.

 

in I5 put Worldwide and in I6 put USA and in the adjacent cell  in J5 put this formula and copy down =SUMPRODUCT(--(ISNUMBER(SEARCH(I5,$E$5:$E$12))),$F$5:$F$12)

 

this will sum based on the condition given and the good part about this is that you do not need to save your excel file with macro enabled option. you can get your results by using built-in Excel functions which is great.

 

please see attached workbook where I embedded the formula in it.

Thanks Jamil, this is super! exactly what I wanted. 

You are welcome Andreea. Thanks for the feedback.

Hi Jamil,

 

I tried to work the count cells by color to work within my workbook, but one sheet isn't working for some reason. I have attached a test version of the document showing the miscounted number. I have 5 red highlights, and it says 2.

 

Thanks,

Hi Carl,

 

 

Looking at your workbook, there is a mismatch between (applied range to) in the conditional formatting and the UDF.

 

If you look at your conditional formatting rules, the red color background rule is applied into three separate noncontinuous range $A$1:$A$54  , range $D$1:$M$54  and range   $B$1:$C$54

while your User Defined function range input is the whole range A1:M54.

The UDF looks up for the match for ranges used in the conditional formatting and the input range inside the function and if there is no match, it will not output correct result.

 

So, if you want to work with the UDF you need to use combination of ranges together in UDF exactly as per the conditional formatting range.  so if you put this =COUNTConditionColorCells($A$1:$A$54,O2)+COUNTConditionColorCells($D$1:$M$54,O2)+COUNTConditionColorCells($B$1:$C$54,O2)

 

it will return 5 which is correct.

 

but if you do not want to use the UDF, then you can acheive the same result by using Excel built-in Functions  like this 

=SUMPRODUCT(--($A$1:$A$54<=(TODAY()-730)))+SUMPRODUCT(--($D$1:$M$54
<=(TODAY()-730)))+SUMPRODUCT(--($B$1:$C$54<=TODAY()-365))

 

The above formula using built-in Excel function will result the same, it returns 5.

 

Hope it helps.

Highlighted

Hi Jamil,

 

I'm trying to count how many cells that are green marked but only gets 0 as the answer even though it should be one. What am I doing wrong? I'm using the vba for counting that you attached earlier in the thread and also conditional formatting.

Hi, I am trying to get a count of each row in your attached example, but when I am passing the row information, it only works correctly for the first row and then the others give the same result. Any way to get this done?

Hi Evelina,

in your workbook, you have set the conditional formatting range differently than the range C5:AX5 you are using in the UD. Also your conditional formatting in column C is just for column C and then from D to the left is $D$6:$AX$6 and this range from column D to AX have not used formula to set up condition formatting. it has used the built in function of "Format only cells that contain" UDF only works if the conditional formatting rule is set using formula option. From what I see in your table, you could easily get the same result using built-in formula of SUMPRODUCT or SUMIFS. Again, your row 3 has n o data, so I would not know what would be the criteria for the formula.

F M

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.

 

 

Hi Jamil, 

 

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 :)

 

Mario

Hello, Jamil

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.

Hi Mario,

 

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.

Hi Zereba,

sorry, I do not have time inspect and add multiple condition formula in your gantt chart template. As you can see in your worksheet Arkusz1 the UDF works. So, making it work on the gantt chart is a matter of putting effort and time. And again, what you are trying to achieve, can be done with Excel built-in Formula similar to the post 204391 link https://techcommunity.microsoft.com/t5/Macros-and-VBA/sum-by-color-when-colors-are-set-by-conditiona...



You are welcome Zareba.

Hi Jamil,

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.Capture.PNG

null null,

it does not work because you perhaps did not read the rule on how to use this UDF. please see my post here https://techcommunity.microsoft.com/t5/Macros-and-VBA/sum-by-color-when-colors-are-set-by-conditiona...

""UDF 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) ""

Hi Jamil,

 

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.

If you are looking for UDF to sum based on index color and not conditional formatted color. then it is even simpler. 

 

you can take the UDF from link here

Hi Jamil,

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.

 

 

Capture1.PNG

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")

 

 

 

 

 

Hi Jamil,

Actual what you are same is Correct, I'm already running VBA code in 10 parts to save CPU Memory.
The second logic with example makes some sense, I will try the same in greater depth of details.
I will let you know the result. Thanks in Advance.

Hi Jamil,

Your suggestion worked perfectly.. Thanks at lot..

You are welcome. glad it worked for you.

Hi jamil,

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..

 

Capture.PNG

Hi Jamil,

Late reply but the colors were indeed the issue for the Test Sheet. For my actual data the UDF doesn't work, even with standard colors. I think it has something to do with the ConvertFormula function in the UDF. My Conditional Formatting uses INDIRECT to determine the cell reference and not a direct cell reference type.

Can this be an issue? And can this be reworked? Workbook hasn't changed.

Cheers, Mario

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? 

Hi Jamil,

 

I have been able to get the UDF to function successfully.  However, when I refresh the data on a different sheet to update that sheet's count of colored cells, the sums change on the previous sheet and/or any other sheet which has summed the cells with conditional formatting.  The cells within the sheet that I refresh on sum correctly, so my conditional formatting should be accurate.  Do I need to establish the UDF to each sheet in the workbook specifically, or is there another way to ensure that the sums calculated on one sheet do not change when a refresh has been run on another sheet?  Would there be anything else I would need to check as to why this is happening?

 

Thank you for your help!


you did not attach anything, besides your question is not relevant to this thread. try posting a new question, 

 

@null null wrote:

Hi jamil,

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..

 

Capture.PNG


 

Hi Steve,

I am not sure if I understood your question.

The UDF calculation method works exactly as the whole workbook calculation like F9 key.
meaning if the calculation is triggered, it will effect all sheets, and not only a specific sheet.
so, if you want the UDF to only calculate on specific sheet, then the code needs to be modified to work on the worksheet level.

Hi Jamil,

 

When I refresh using F9, the current sheet will show the correct data in the output cell, however this appears to interfere with the output cells on other sheets in the workbook which are trying to sum the color condition using a range of cells on their respective worksheet.  I have attached a sample of the workbook I'm working on to hopefully better illustrate the situation.  Perhaps this is an issue with my conditional formatting formulas, however each sheet is calculating the correct number of colored cells on its own, leading me to believe that this is not the case.  Could you take a look and suggest what I might do to fix this?  If it requires a UDF for each sheet individually, could you help me with a rewrite to make the code sheet specific?

 

Thank you very much for your time!

Hi Steve,

 

thanks for uploading the sample data.

 

Can you please test the attached version?

 

 

Hi Jamil,

 

I am running into an invalid name error with each COUNTConditionColorCells output cell stating that the formulas contain unrecognized text.  I did enable macros on the sheet after downloading it.  However, I can see from the document preview that the first 2 sheets are calculating correctly for both green and red text cells, while the 3rd and 4th sheets are calculating green text cells properly but not the red text cells (sheet 3 shows 6 while it should be 9, sheet 4 shows 10 while it should be 7).

 

Thank you.

Jamil,

 

Please disregard my previous message.  After I enabled Macros, saved, and closed the document, I opened it back up and it was running the formulas properly.  However, I did have to double click the cell and press Enter to refresh the formula so that it would calculate the correct amount.  Regardless, the numbers work and they do not change when I do this to other output cells on separate sheets.  I will need to add what you did on these 4 sheets to several other sheets to get them to work properly as well.  Could you let me know what you changed so that I can make the same edit to the full workbook and its additional sheets?

 

Thank you!

Thanks for the feedback Steve.

I didn’t change anything in the sheets.

Except that I removed one line from the beginning of the code which was
Application.Volatile

That’s it.

If you add more worksheets there should not be any issue.

Jamil,

 

I have made this adjustment after adding more sheets and all is working just as it should.  Thank you so much for all your help on this issue!

You are welcome Steve.

Thanks for the feedback.

Hi Jamil,

 

Thank you for sharing your VBA code.

I have copied your code in my excel file, but I got #VALUE! errors.

Could you check what's wrong with the file?

Thank you for your help in advance.

 

 

 

Jamil I have the same issue as Hyosun.  I've entered the VBA code but cannot get it to work.  No matter what I do it keeps showing the #Value.  I'm trying to count duplicate text based values which seems to be causing some problems. Can you point me towards where the error might be occurring and or a resolution.

 

Thanks

 

Noel

Hi Hyosun Ko,

 

the reason it did not work is, because you used ranges outside the scope range and worksheet in the formula that set conditional formatting.

 

UDF is not designed to handle such complicated formulas outside the scope of CF.

 

however, the good news is that you do not have to use the UDF to ge tthe result which you need.  you can use the built-in SUMPRODUCT function to get the sum based on your conditions exactly like the CF.

 

so for sum of Cabinet use =SUMPRODUCT(G$11:G$20,--(G$11:G$20>0),--ISNUMBER(MATCH($B$11:$B$20,Cabinet_recharge,0)))

 

and for base =SUMPRODUCT(H$11:H$20,--(H$11:H$20>0),--ISNUMBER(MATCH($B$11:$B$20,Base_recharge,0)))

 

for colour =SUMPRODUCT(J$11:J$20,--(J$11:J$20>0),--ISNUMBER(MATCH($B$11:$B$20,Colour_recharge,0)))

 

for installation =SUMPRODUCT(K$11:K$20,--(K$11:K$20>0),--ISNUMBER(MATCH($B$11:$B$20,Installation_recharge,0)))

 

for delivery =SUMPRODUCT(L$11:L$20,--(L$11:L$20>0),--ISNUMBER(MATCH($B$11:$B$20,Delivery_recharge,0)))

 

I have embedded these formulas in the attached workbook.

Hi Noel,

The VALUE error because your conditional formatting was not set using formula.

If you read my earlier post, I have highlighted that UDF only works if the conditional formatting is set by using formula and not the built-in features of CF.

 

So, in the attached workbook, I have set the Conditional Formatting using formula and then the UDF works.

 

please note that SUBTOTALs are not counted as duplicate.   please see attached file and examine it by yourself.

 

Hope it helps.

 

 

Thank you Jamil- so easy when you know how.  Much appreciated.

Dear Jamil,

 

Your help has been really enlightening. If you are available, could you please help me by taking a look at his file?

I am using the VB code you created, but the formula doesn't seem to recognize the CF color and I keep getting the #VALUE! refference.

Could you let me know how to fix this?

 

 

Dear David,

 

The issue with your file was, that UDF was using single row range as the range input, while the Conditional Formatting was applies from L2 to Z8 .   So, i modified the file Conditional Formatting and now it works. 

 

Also inside the CF formula, you used two functions, while this was not cause of error, but to make it simple and faster, i simplified it with a single function of "Columns".

 so =COLUMN(L2)-COLUMN($L2)+1>=$AE2  became =COLUMNS($L2:L2)>=$AE2

 

 

Please download and see attached workbook.  

 

This is awesome, thank you so much for your help!

Jamil -  This is a great tool, however, is it possible to make it work for 3 colors?  The counts and sums are inaccurate for me after the first color when there are more than 2.  Thank you!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies