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

- 506K Members
- 9,760 Online
- 603K 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.

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

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

09-19-2018 02:37 PM

Matt,

it would work with any number of colors, as long as the UDF parameter color input and CF are having the same color. if your file does not produce correct result, can you attach a sample file so that i can take a look?

it would work with any number of colors, as long as the UDF parameter color input and CF are having the same color. if your file does not produce correct result, can you attach a sample file so that i can take a look?

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

09-20-2018 04:21 AM

Thank you, I looked at this further and realized my conditional formulas overlapped but their organization was allowing the formatting to be correct. I am able to change the formulas and get the correct results.

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

09-20-2018 05:20 AM

Thanks for the feedback Matt. Glad to hear you figured it out by yourself.

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

09-27-2018 06:52 AM

Hello,

I have a list of expenses that are separated into categories. When you select a category from the dropdown, it changes background color for the expense amount. I am trying to add all these up automatically at the bottom. I have attached a sample.

Any ideas if there is a easier solution, or what I should do?

I tried your second code you posted on this thread, when I try to add the "car" category, it returns with a straight $100. Dosnt add anything. Any help would be great. Thank you very much!

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

09-27-2018 06:59 AM

Hi Shane.

you can simply do that with SUMIF function.

I have embedded the formula into the attached workbook.

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

09-27-2018 07:05 AM

Wow, I was WAY overthinking this problem...You're reply was very very quick, thank you.

I was looking on page 2 and found another solution you gave "=SUMPRODUCT(--(ISNUMBER(SEARCH("Car",$C:C))),$D:D)" But the sumif is way less complicated. Thank you!

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

09-27-2018 08:06 AM

You are welcome Shane. Thanks for the feedback.

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

10-06-2018 08:16 PM

Hi Jamil,

Thanks for your patience and replying to queries posted around this subject. Appreciate it. I have been trying to apply the VBA function you had shared at the beginning of the thread. I have a slightly complicated conditional formatting set (cell to be highlighted yellow if there are 3 consecutive zeros, red if there are 4 or more consecutive zeros). I am not sure if it is due to complicated conditional formatting, but I get an error #Name? as I try to count number of cells in red and yellow. When I rerun, sometimes I get a value but the number displayed is incorrect. Could you help resolve - thanks a lot. I have attached the excel.

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

10-08-2018 11:58 AM

Hi Anand ,

I could not debug your file, aas you had several conditional formatting with deleted reference with #REF error inside your CF formulas.

However, i tried to come up with a solution using helper columns and built-in Excel functions. I have attached the file.

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

10-08-2018 09:10 PM

Thanks a lot for the reply, Jamil. This is very helpful. Thank you for taking the time to help. A quick question, for some reason, when we double click or change the range in the helper column formula, it changes to #value. I am not sure why. If you have any thoughts, please let me know. I really appreciate your support.

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

10-08-2018 10:17 PM

It is because those formulas are not simple formulas and they are called Array formula which needs to be entered by holding Control + Shift then+ Enter

So if you simply enter it will give Value error but if you enter it with hold together control shift enter then it will work.

So if you simply enter it will give Value error but if you enter it with hold together control shift enter then it will work.

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

10-09-2018 07:01 PM

Got it. Thanks a lot, Jamil. Again, really appreciate all your help and quick responses.

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

10-11-2018 01:48 PM

Hello, Jamil,

First I'd like to thank you for answering all those questions!

I've been trying to use the count formula in a worksheet but I always get the #Value error. I read the whole thread and I couldn't figure out what I'm doing wrong. Could you please help me?

This spreadsheet has grades of my students in three different periods and I wanna check how much green and purple boxes there are in each row.

Thanks in advance for your attention,

José Gabriel

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

10-20-2018 06:00 AM

Hello Jamil,

I am having some issues when using the VBA codes you provided.

I have three types of cells I am looking to count. All three have the same fill color (GREY) but different font colors(RED,PURPLE,BLUE). Ultimately I would like to have a table that counts all similar fill colors (would include all three types).

It is difficult to explain the errors I'm getting so I included as much information in the attached excel as possible.

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

10-23-2018 08:25 AM

Hi Jose,

You were close, but your conditional formatting range area was different than what was used in the UDF. So, I modified the conditional formatting area range and used format painter to replicate it in other cells and it works now. I have attached the file here.

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

10-23-2018 09:03 AM

Hello Sylvia,

There is no need to overkill this with UDF. We can simply use the built-in Excel formulas to achieve what you are trying to get.

This formula I wrote for you is maybe not easy ;) but it does the job and it is also dynamic, so if your year changes, it will change. I mean the cell I2 for start of the period and AG29 the end of the period.

Please see attached file which I embedded the formulasin range AO20:AO23

For counting the PTO vacation days Formula =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT(DATEVALUE($I$2)&":"&DATE(YEAR(DATEVALUE(AG29)),MONTH(DATEVALUE(AG29)),31))),$B$3:$B$35,0)))

For counting Federal Holidays Formula =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT(DATEVALUE($I$2)&":"&DATE(YEAR(DATEVALUE(AG29)),MONTH(DATEVALUE(AG29)),31))),$J$12:$J$26,0)))

For counting Floating Holidays Formula =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT(DATEVALUE($I$2)&":"&DATE(YEAR(DATEVALUE(AG29)),MONTH(DATEVALUE(AG29)),31))),$J$30:$J$37,0)))

For counting the overall then Formula =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT(DATEVALUE($I$2)&":"&DATE(YEAR(DATEVALUE(AG29)),MONTH(DATEVALUE(AG29)),31))),$B$3:$B$35,0)))+SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT(DATEVALUE($I$2)&":"&DATE(YEAR(DATEVALUE(AG29)),MONTH(DATEVALUE(AG29)),31))),$J$12:$J$26,0)))+SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT(DATEVALUE($I$2)&":"&DATE(YEAR(DATEVALUE(AG29)),MONTH(DATEVALUE(AG29)),31))),$J$30:$J$37,0)))

Hope it helps.

Best regards,

Jamil

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

10-25-2018 06:53 PM

Jamil,

You are simply amazing. One additional ask:

I want to count the unique "adjoining weekends" of all the vacation times I entered. I couldn't figure out a "clean" way to do this so I just added additional columns of formulas that I will hide from view.

I used the same formula you provide to count these days but it doesn't count the unique days, but rather all the days in the designated range even though there are duplicates.

I attached the file so you could see my updates. Hoping this is possible.

Thanks,

Sylvia

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

10-27-2018 04:05 PM

Hi Sylvia,

I have modified your helper column formulas and then used below formula in AP6 cell to count unique.

Please remember that this formula is array and when entering it, requires the special keystroke of Control+Shift+Enter

Please see attached file, the completed version.

=SUM(--(FREQUENCY(HolidayWeekends,HolidayWeekends)>0))+SUM(--(FREQUENCY(HolidayWeekends2,HolidayWeekends2)>0))+SUM(--(FREQUENCY(PTOW,PTOW)>0))+SUM(--(FREQUENCY(PTOW2,PTOW2)>0))+SUM(--(FREQUENCY(FloatingW,FloatingW)>0))+SUM(--(FREQUENCY(FloatingW2,FloatingW2)>0))

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

11-01-2018 02:17 AM

Hi Jamil!

Thanks for sharing code!

Can this code be modified so it works also with only one CF cell?

Currently it kind of works also with one cell, but only if function is refreshed by pressing "Enter" on particular cell, where UDF is located.

Thanks for sharing code!

Can this code be modified so it works also with only one CF cell?

Currently it kind of works also with one cell, but only if function is refreshed by pressing "Enter" on particular cell, where UDF is located.

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

11-01-2018 05:25 AM - edited 11-04-2018 03:47 PM

Hi Matiss,

The UDF already works with "only one CF cell"

attached is the workbook that demonstrates.

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

11-02-2018 02:11 PM

Hi Jamil,

request for your help in the following problem.

I need the help to write the VBA code or standard function to do the SUMIFS - with both criteria as colour and value.

Urgent - response requested.

Shoaib

request for your help in the following problem.

I need the help to write the VBA code or standard function to do the SUMIFS - with both criteria as colour and value.

Urgent - response requested.

Shoaib

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

11-04-2018 04:14 PM

Hi Shoaib,

though your question is not related to this thread, I will try to answer it.

In your example, you can make your SUMIFS work by using a helper column and also a UDF which is embedded in the attached example file.

So the helper column return the color index number of cell background as you can see in the attached example and then I used the UDF in the helper column to return the color index value and then used that range as the criteria range for the SUMIFS function. Please note that the example is only set to work for the background cell color set by user and not via Conditional formatting.

Besides, there is also another way to get the same result without using SUMIFS.

simply use auto filter and filter by the desired color and then by your desired value and then use the built in Subtotal function to sum only filtered cells like this =SUBTOTAL(109, yoursumrange)

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

11-09-2018 11:14 AM

Hi Jamil,

I sent you a msg, before discovering this thread is still active. So I am attaching the file,

My conditional formatting evaluates row 18 and colors cells A3:G16 if the corresponding A18:G18 value doesnt match....

but as you can see something is really hosed up.

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

11-09-2018 11:57 AM

Jamil,

This is my real data (extract--no names and a few rows/cols)....What I really want to do is the conditional formatting as is done correctly (red values where the evaluated value doesn't match row 7) and then count the red values per row (col a)

I tried in a xlsm and xls...neither has worked.

thank you so much,

dg

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

11-09-2018 12:27 PM

Hi dg

I am not home now. Once I reach home, I will take a look at your excel file.

I am not home now. Once I reach home, I will take a look at your excel file.

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

11-11-2018 01:56 PM

Hi dg,

I could not understand the second file you shared, as the conditional formatting areas were inconsistent with the range used in the UDF.

However, I understood that you are trying to count based on the font color not the background color. therefore, I made some modification to the UDF and embedded it in the attached workbook.

as you can see based on your first file. it works.

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

11-11-2018 04:14 PM

Thanks a lot, Jamil!

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

11-11-2018 04:21 PM

You are welcome José.

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

11-11-2018 08:40 PM

Dear Jamil,

thanks a lot for your favor and response, it works ok. I also have received another solution (Bosco);

1] Define 2 Names :

Select B17 >> Define Name >>

Name (1) : SumRgn

Refer to :

=INDEX($D$5:$I$10,MATCH($A17,$A$5:$A$10,0)+MATCH(LOOKUP("zz",$B$15:B$15),{"Onsite";"OffSite"},0)-1,0)

OK

Name (2) : SumColor

Refer to :

=SUMPRODUCT(0+(GET.CELL(63,IF(1,+OFFSET(SumRgn,,COLUMN($D$4:$I$4)-MIN(COLUMN($D$4:$I$4)))))=GET.CELL(63,B$16)),SumRgn)

OK

2] In B17, formula copied across to G17 and all copied down :**=**SumColor

p.s. : Get.Cell() is a Excel 4 Macro function, so you need to save file as Macro-Enable Workbook xlsm type.

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

11-11-2018 08:44 PM

Dear Jamil,

thanks a lot for your favor and response, it works ok.

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

11-12-2018 04:57 AM

Dear Shoaib,

You are welcome. Thanks for your feedback.

You are welcome. Thanks for your feedback.

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

11-12-2018 06:13 AM

thanks Jamil,

You provided a total count , but I wanted row counts. I altered your example, but it requires copying formatting of the first row and then individually copying to each row there after.

my real sheet has 380 rows....is there a faster way.....if I select all of the rows then the conditional formatting selection inst like the udf and it bombs

Highlighted

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

11-12-2018 06:45 AM

Hi dg,

your conditional formattings in the attached file had some issues and I have fixed them in the attached version. so the UDF returns correct result.

regarding your question on 380 rows. You do not need to recreate condtional formatting for each of the rows. you can use the format painter option with use of double click to be able to replicate the conditional formatting on your rows.

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

11-14-2018 06:04 AM

Again Thank you Jamil,

I do not know what I keep messing up, but when I did the exact stuff to the real file , I keep getting no color.....I am attaching the real file so should there be some "file" setting differences maybe we can resolve.

I am wanting to know how many by row based on the color used by the conditional formatting

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

11-16-2018 08:31 AM

Hi DG,

There were three issues with your workbook. A) it was based on the font color, which I have modified the UDF to take the font color. B) your formula in conditional formatting J8<>J$7 was not taking care of the blank cells, so it was also coloring the font of the blank cells, therefore, I have wrapped it inside AND function like this =AND(J8<>J$7,J8<>"")

C) Your range inside the UDF was per row, while your conditional formatted range was the entire range.

So, I have changed the conditional formatting from the entire range to the single row using format painter.

Please see attached workbook that is all set.

Hope it helps.

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

11-18-2018 01:24 PM - edited 11-18-2018 01:48 PM

Hello Jamil, Can you please help? I need to count all cells in a range (row or column, but in this case is a row) that have been conditionally colored. I tried following your instructions but I get "0". I have 6 (six) clusters of data (ranges) which I have assigned a range name. The conditional formatting formula finds all cells which value repeats in all clusters (if the number does not repeat in ALL clusters then the cell does not get highlighted). I need to COUNT per row how many cells are highlighted, and I have to be able to do the same with all rows individually. Please see attached file. Can you do it in my file and explain what is wrong? Thank you Jamil.

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

11-19-2018 02:20 AM

Hi Z Z,

Your conditional formatted area was not exactly as per your range used in the UDF.

If you want to count per row, then your CF should be per row. I have modified the CF and now it works.

plz see attached.

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

11-19-2018 08:14 PM

Jamil,

Thank you!!!!! It works as I needed it.

Have a wonderful day.

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

11-20-2018 02:05 PM

Z Z

Glad to hear that it works. Have a wonderful day too. thx for the feedback.

Glad to hear that it works. Have a wonderful day too. thx for the feedback.

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

11-22-2018 08:40 PM - edited 11-22-2018 08:45 PM

Jamil, thank you for helping us...

I have six clusters of data, each is 11 columns by 70 rows.

There is Conditional Formatting in each cluster to the effect that if on another cell (say N2) I type a value and that value is found within all the six clusters of data then all the cells containing that manually inputted value will be highlighted (to a specific color that I already set).

Up to that point all is fine.

Now, I need to display MATCH FOUND when all cells in a row (in one cluster of data) have been conditionally formatted and change the font color to all the cells that meet the criteria.

I know it sounds confusing... I'm dizzy myself... but to illustrate the scenario, I attached a file with dummy data.

In the attached file, to illustrate my point, I manually changed the FONT COLOR to all the cells that meet the criteria (all the cells in that row have been conditionally formatted with different colors, obviously), and I also manually added at the bottom of each cluster of data a MATCH FOUND cell since that's what I also need to see as I manually inputted specific numbers (in another area of the sheet, N2 through N7) that I'm looking for.

I tried with the following formulas but to no avail (the VB module is also in place)...

=REPT("MATCH",COUNT(1/MMULT(TRANSPOSE(ROW(P$2:Z$71))^0,N(P$2:Z$71=O2)))=11) array formula

=IF(SUM(--(MMULT(TRANSPOSE(ROW(P$2:Z$71))^0,--(O2=P$2:Z$71))>0))=11,"MATCH","") regular formula

In the examples I have found in this forum, they count how many cells have been conditionally formatted. My scenario is slightly different: I need to be able to SEE when all the cells in row of a cluster of data have been conditional formatted and to display "MATCH FOUND" and change the font color of those cells.

Thank you for helping.

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

11-26-2018 10:01 AM

Hi Z Z,

I have already provided you the solution based on your template and you have confirmed that it worked.

link here https://techcommunity.microsoft.com/t5/Excel/sum-by-color-when-colors-are-set-by-conditional-formatt...

Your latest file have several issues; CF used without use of formula and formulas outside the CF range and many named ranges with broken links that and requires time to debug and analyse, the time which I do not have. As I only contribute to the extend possible and I cannot dive deep and build solutions.

You can post new questions as a new thread on this forum, perhaps other Experts might be able to help you. Or if you need an instant solution, you can hire a Dev for an hour or so, to make this work for you.

Thanks for your understanding.

I have already provided you the solution based on your template and you have confirmed that it worked.

link here https://techcommunity.microsoft.com/t5/Excel/sum-by-color-when-colors-are-set-by-conditional-formatt...

Your latest file have several issues; CF used without use of formula and formulas outside the CF range and many named ranges with broken links that and requires time to debug and analyse, the time which I do not have. As I only contribute to the extend possible and I cannot dive deep and build solutions.

You can post new questions as a new thread on this forum, perhaps other Experts might be able to help you. Or if you need an instant solution, you can hire a Dev for an hour or so, to make this work for you.

Thanks for your understanding.

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

11-26-2018 04:55 PM

Jamil,

Thanks for replaying.

It's true... on a previous post I said that the code you gave me works and I stand by that.

The request after that was of a different scenario... the formulas need to be applied in a totally different way...

Also, I already deleted all the CF... the file I send earlier belongs to a bigger workbook where all that conditional formatting is used and some how it stayed (I forgot to check and clean) in this file and the CF even linked to a Sheet4... The CF in the file now is only what's needed.

But no worries... I already figure it out...

Please see attached WORKING file... I upload it here for reference in case any one else might need the same solution.

If you inspect the file, you will see that is not the same problem although is has to do with counting with conditional formatting... which is what this thread is about.

Thanks again Jamil.

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

11-27-2018 05:03 AM

Glad to hear that you have already figured it out. Thanks for the feedback.

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

12-05-2018 02:28 PM

Hi Jamil,

I would like to count the number of coloured cells in a set of data regardless of the numbers within the coloured cells. These colours are determined by conditional formatting. The colours include green, amber and red.

I have applied your VBA code to count colours, however it does not count correctly. It only counts the amber coloured cells from the data set.

From the dummy data I have attached, the number of reds should be 2, the number of ambers should be 1 and the number of greens should be 2.

Could you please have a look at the dummy data attached to determine why there is an issue and best appropriate actions.

Thanks in advance,

Waran.

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

12-07-2018 06:52 AM

Hi Jamil,

I have tried everything and even inserted my sheet into your uploaded file but the count colours function only returns 0

Please help

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

12-07-2018 07:15 AM

Hi waran,

I checked your file. you have sent Conditional formatting for the same color (i.e red) on the same range. if you have multiple condition for the single color. then it needs to be put in one single formula, with OR function. the UDF does not work on multiple condition set for the same color with two different CF rule.

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

12-07-2018 07:17 AM

Hi

If you read my earlier posts. you will see that I mentioned that for the UDF to work. CF needs to be creating using formula. I checked your CFs are not creating using Formula and that is why it does not work. If you change your CFs to be based on formula then it will work.

If you read my earlier posts. you will see that I mentioned that for the UDF to work. CF needs to be creating using formula. I checked your CFs are not creating using Formula and that is why it does not work. If you change your CFs to be based on formula then it will work.

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

01-16-2019 07:09 AM

Hi Jamil, I believe I've done everything properly yet it still displays "#VALUE!" please help me.

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

01-16-2019 08:02 AM - edited 01-16-2019 08:03 AM

Hi Julius835,

There are several issues with your workbook that is why UDF result in error.

I thought, I could explain it better by recording a video. Please see attached workbook and the video.

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

01-16-2019 12:09 PM

Jamil,

Thank you for your response. I have made the necessary changes and now my issue is if I try to count the cells only in certain rows I get an incorrect number.

Related Conversations

Stable version of Edge insider browser

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

10 Likes

35 Replies

How to Prevent Teams from Auto-Launch

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

8 Likes

32 Replies

Security Community Webinars

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

18 Likes

15 Replies

Discussion - Updating our interface with Fluent touches

Elliot Kirk
in
Discussions
on
01-13-2020
5,390
Views

35 Likes

102 Replies

Share

Popular

Learning Resources

Programs

Values

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