Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 442K Members
- 13.6K Online
- 531K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- Re: sum by color when colors are set by conditional formatting

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

SOLVED
Home
## sum by color when colors are set by conditional formatting

- Home
- :
- Excel
- :
- General Discussion
- :
- Re: sum by color when colors are set by conditional formatting

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

matt nipper

New Contributor

12-20-2016
01:16 PM
- last edited on
07-25-2018
09:33 AM
by
TechCommunityAP

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

Labels:

215 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

Best Response confirmed by
Jamil Mohammad (Super Contributor)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Tags:
- SUMCFORMAT

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-12-2017 05:58 AM

You are welcome. Thanks for the feedback

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-27-2017 10:22 PM

Hi Jamil,

I have a GANT diagram for were I want to sum the values in column cells with a specific colour.

This is to sum how many labour hours we have to make each day for a specific background color that was generated by the conditional formatting.

In this GANT diagram a pop up boxt can not be used. Do you have a solution for that?

I have a GANT diagram for were I want to sum the values in column cells with a specific colour.

This is to sum how many labour hours we have to make each day for a specific background color that was generated by the conditional formatting.

In this GANT diagram a pop up boxt can not be used. Do you have a solution for that?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-25-2018 01:53 AM

Is it possible for cell count instead of sum. Please share the VBA code if possible.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-11-2018 09:22 AM

Hello Bethany,

Yes, I have attached it here.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-15-2018 02:15 PM

Jamil, does this work for the color WHITE as well? i seem to get the error "#VALUE!" when using the SumConditionColorCells formula. Other colors seems to work.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-19-2018 03:03 AM

"does this work for the color WHITE as well? "

There are two things not to mixup the WHITE background color with No-Background Color.

If your cells have conditional formatting and the color is selected is plaint white then yes it works. but if the cells have No color meaning it the background color is None then it does not work.

RE: on your second question:

you need to check the colors of your conditionally formatted cells and the color of criteria. they must match 100%. sometimes, the colors look similar while they are different, best way to guarantee you are using the exact same color is to find that specific color's color code and that you can do by using VBA to find the color code of your cells.

If you do not want to go in that direction then i suggest you rebuild your conditional formatting cells by selecting a simple color and then also use the same color in the criteria cell.

in case if you wanted to find out the color code with VBA then here is a link https://www.thespreadsheetguru.com/the-code-vault/2014/11/5/retrieve-excel-cells-font-fill-rgb-color... that explains how.

There are two things not to mixup the WHITE background color with No-Background Color.

If your cells have conditional formatting and the color is selected is plaint white then yes it works. but if the cells have No color meaning it the background color is None then it does not work.

RE: on your second question:

you need to check the colors of your conditionally formatted cells and the color of criteria. they must match 100%. sometimes, the colors look similar while they are different, best way to guarantee you are using the exact same color is to find that specific color's color code and that you can do by using VBA to find the color code of your cells.

If you do not want to go in that direction then i suggest you rebuild your conditional formatting cells by selecting a simple color and then also use the same color in the criteria cell.

in case if you wanted to find out the color code with VBA then here is a link https://www.thespreadsheetguru.com/the-code-vault/2014/11/5/retrieve-excel-cells-font-fill-rgb-color... that explains how.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-19-2018 11:17 AM

Can you post a screenshot of dummy data or a dummy excel file?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-23-2018 03:28 AM

Hi Steve,

I have modified the code and attached is the version that counts instead of sum.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-26-2018 10:03 AM

SUMIFS and SUMIF I don't think work. The problem as far as I can see is
that conditional formats are volatile and must calculate after all cells
have calculated. Therefore you cannot have a function that will sum the
displayformat color easily.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-26-2018 10:37 AM

Still getting the same error, thank you for taking a look, it's attached.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-27-2018 08:19 AM

Hi Cara,

I looked the file you attached. there were many issues that probably caused that error.

A) The workbook had No UDF in it. hence NAME? Error

B) this UDF has two limitations 1- 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 I could see in the workbook you shared that there were two rules which was not determined by formula 2- Second the UDF will only work if sum range is more than one cell in another word, it will not sum a single cell or if Conditional formatting rule is applied in range that is a single cell. In your workbook there were multiple rules that applied in single cells.

As I suggested in my earlier posts, If the UDF works, then fine, if does not work then it is not the only and optimal solution. So, rather than Counting based on the outcome of the conditional formatting, you can use the same criteria as exists in the conditional formatting to Count the cells using COUNTIFS or SUMPRODUCT

I didn't examine in in depth your workbook as the data validation were linked to another workbook and for me it was showing a broken link. You could use the conditional formula to return values in column G and based on the values you could simply count the overdues with simply formula like this =COUNTIF(G9:G28,G5)

I looked the file you attached. there were many issues that probably caused that error.

A) The workbook had No UDF in it. hence NAME? Error

B) this UDF has two limitations 1- 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 I could see in the workbook you shared that there were two rules which was not determined by formula 2- Second the UDF will only work if sum range is more than one cell in another word, it will not sum a single cell or if Conditional formatting rule is applied in range that is a single cell. In your workbook there were multiple rules that applied in single cells.

As I suggested in my earlier posts, If the UDF works, then fine, if does not work then it is not the only and optimal solution. So, rather than Counting based on the outcome of the conditional formatting, you can use the same criteria as exists in the conditional formatting to Count the cells using COUNTIFS or SUMPRODUCT

I didn't examine in in depth your workbook as the data validation were linked to another workbook and for me it was showing a broken link. You could use the conditional formula to return values in column G and based on the values you could simply count the overdues with simply formula like this =COUNTIF(G9:G28,G5)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-11-2018 11:13 AM

Thank you Jamil,

Is there any solution for that?

Appreciate your help.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-21-2018 02:51 AM

Hey Jamil,

the code is not working on my file, could you please help me.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-24-2018 08:49 AM

Hi ogyramly,

Please read the earlier posts through this thread and try the suggestion given and the limitations of the UDF, i suspect that one of the issues already known could have caused the UDF to return no color.

Please read the earlier posts through this thread and try the suggestion given and the limitations of the UDF, i suspect that one of the issues already known could have caused the UDF to return no color.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-25-2018 03:27 AM

Hi Fenfen,

It is because the conditional formatting formula of Vlookup uses the same range that is used in the formula on the cells that produce the numeric results, so it is kinda create circular reference in a nontraditional way. The UDF is not designed to handle this sort of complex scenario. the UDF works on the simple conditional formatting that would use a formula that evaluate cells that has either simple values or if they have formulas, they are not using the same range as the formula used in the conditional formatting.

I suggest you use the other methods described in the earlier posts, like combination of SUMPRODCUCT with COUNTIFS

It is because the conditional formatting formula of Vlookup uses the same range that is used in the formula on the cells that produce the numeric results, so it is kinda create circular reference in a nontraditional way. The UDF is not designed to handle this sort of complex scenario. the UDF works on the simple conditional formatting that would use a formula that evaluate cells that has either simple values or if they have formulas, they are not using the same range as the formula used in the conditional formatting.

I suggest you use the other methods described in the earlier posts, like combination of SUMPRODCUCT with COUNTIFS

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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?

Related Conversations

Stable version of Edge insider browser

HotCakeX
in
Discussions
on
10-12-2019
33.4K
Views

6 Likes

35 Replies

flashing a white screen while open new tab

Deleted
in
Discussions
on
10-05-2019
30K
Views

14 Likes

14 Replies

How to Prevent Teams from Auto-Launch

chenrylee
in
Microsoft Teams
on
06-27-2019
165K
Views

8 Likes

29 Replies

Security Community Webinars

Valon_Kolica
in
Security, Privacy & Compliance
on
10-22-2019
12.4K
Views

9 Likes

9 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © Microsoft