sum by color when colors are set by conditional formatting

[matt nipper]

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

[#2]

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

[#3]

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)

[#4]

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.

[#5]

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 

[#6]

Hi dg

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

[#7]

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. 

[#8]

Thanks a lot, Jamil!

[#9]

You are welcome José.

[#10]

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.

[#11]

Dear Jamil,

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

[#12]

Dear Shoaib,

You are welcome. Thanks for your feedback.

[#13]

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

[#14]

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.

[#15]

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 

[#16]

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.

[#17]

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.

[#18]

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.

[#19]

Jamil,

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

Have a wonderful day.

[#20]

Z Z

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

[#21]

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.