09-19-2018 02:37 PM
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.
09-20-2018 05:20 AM
Thanks for the feedback Matt. Glad to hear you figured it out by yourself.
09-27-2018 06:52 AM
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!
09-27-2018 06:59 AM
you can simply do that with SUMIF function.
I have embedded the formula into the attached workbook.
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!
10-06-2018 08:16 PM
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.
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.
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.
10-08-2018 10:17 PM
10-09-2018 07:01 PM
Got it. Thanks a lot, Jamil. Again, really appreciate all your help and quick responses.
10-11-2018 01:48 PM
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,
10-20-2018 06:00 AM
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.
10-23-2018 08:25 AM
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.
10-23-2018 09:03 AM
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.
10-25-2018 06:53 PM
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.
10-27-2018 04:05 PM
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.
11-01-2018 02:17 AM
11-01-2018 05:25 AM - edited 11-04-2018 03:47 PM
The UDF already works with "only one CF cell"
attached is the workbook that demonstrates.
11-02-2018 02:11 PM
11-04-2018 04:14 PM
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)
11-09-2018 11:14 AM
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.
11-09-2018 11:57 AM
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,
11-09-2018 12:27 PM
11-11-2018 01:56 PM
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.
11-11-2018 08:40 PM
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 :
Name (2) : SumColor
Refer to :
2] In B17, formula copied across to G17 and all copied down :
p.s. : Get.Cell() is a Excel 4 Macro function, so you need to save file as Macro-Enable Workbook xlsm type.
11-11-2018 08:44 PM
thanks a lot for your favor and response, it works ok.
11-12-2018 06:13 AM
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
11-12-2018 06:45 AM
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.
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
11-16-2018 08:31 AM
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.
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.
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.
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.
11-26-2018 10:01 AM
11-26-2018 04:55 PM
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.
12-05-2018 02:28 PM
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,
12-07-2018 06:52 AM
I have tried everything and even inserted my sheet into your uploaded file but the count colours function only returns 0
12-07-2018 07:15 AM
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.
12-07-2018 07:17 AM
01-16-2019 07:09 AM
Hi Jamil, I believe I've done everything properly yet it still displays "#VALUE!" please help me.
01-16-2019 08:02 AM - edited 01-16-2019 08:03 AM
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.
01-16-2019 12:09 PM
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.
by sootheng on May 30, 2020
by Snuffles00 on May 29, 2020