sum by color when colors are set by conditional formatting

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?

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.

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

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!

Hi Shane.

you can simply do that with SUMIF function.

I have embedded the formula into the attached workbook.

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!

You are welcome Shane. Thanks for the feedback.

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.

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.

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.

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.

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

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

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.

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.

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

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

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

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.

Hi Matiss,

The UDF already works with "only one CF cell"

attached is the workbook that demonstrates.

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

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)

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.

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

Hi dg

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

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.

Thanks a lot, Jamil!

You are welcome José.

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.

Dear Jamil,

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

Dear Shoaib,

You are welcome. Thanks for your feedback.

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

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.

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

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.

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.

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.

Jamil,

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

Have a wonderful day.

Z Z

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

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.

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.

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.

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

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.

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

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.

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.

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

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.

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.