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

Showing results for

- 446K Members
- 8,113 Online
- 538K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

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

Showing results for

SOLVED
Home
## How to count and sum "Condtional formatting" cells by color in Excel 2010?

- Home
- :
- Excel
- :
- General Discussion
- :
- Re: How to count and sum "Condtional formatting" cells by color in Excel 2010?

- 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

01-18-2019 03:46 PM

Good evening Mr. Jamil Mohammad,

I read your post and downloaded it. It works well.

But I would have a question of how to solve each line separately for each color.

Thank you for your reply and help in advance, a nice evening.

Example:

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

01-21-2019 03:53 AM

Hi Norbi,

I will respond to your post due to the fact that i had the same challenge as you, and Jamil showed me the way around this.

If you check the previous post in which Jamil replied to me you will see that the CF range need to be exactly the row for which you need to make the count. you cannot use a range like A3:G16 because it won't work. The correct way to do it is to set like this: A3:G3. - for the first row.

And afterwards you need to use the format painter and to paste the CF formulas row by row. As a tip, if you want to make multiple formats with the format painter you can double click the format painter and it will remain active for all the pastes.

Jamil also made a video with this step in the post he replied to me.

i have attached the modified workbook

i hope it helps

Alin

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

01-27-2019 12:48 AM

Hello Alin20

thank you very much for your answer managed to solve my problem. I realized that there were a lot of conditional formatting and could not count the colors correctly.

Thank you very much for your help again.

Hi,

norbi

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

02-05-2019 09:33 PM

Hi Jamil,

The example that you helped me with worked like a charm but only for one conditional format formula applied for a range of cells.

Now i have a case in which i want to count all the CF cells in a row, but with multiple CF formulas.

I followed all the steps that you suggested (i hope i didn't missed something out).

I have attached the worksheet.

Thank you in advance

Alin

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

02-13-2019 01:31 PM

Hi Alin_20.

You did not properly set your conditional formatting in your file, that is why it was not working.

I set the conditional formatting in the attached file correctly and it works now.

plz see attached.

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

02-18-2019 12:32 PM

When I use the formula, it works rows in the column except for the 2nd row of data. It does not count that row. Has anyone hit this issue or found a way around it? It correctly counts all of the other rows so I know it is looking at the condition formatting.

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

02-19-2019 11:34 AM

It should work. i suspect there must be an issue with the way CF range is set.

if you upload a dummy sample, i can take a look.

if you upload a dummy sample, i can take a look.

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

03-29-2019 05:31 AM

Good morning Jamil! This thread has been SO helpful! I really appreciate it. Of course, since I'm joining I'm having trouble lol.

I added the UDF and made sure that the CF I'm using is based on formulas however I can't seem to get the results to be stable.

This is a fairly simple application and I'm feeling pretty dumb right now as I'm sure whatever it is I'm doing wrong is an easy fix.

Would you mind terribly taking a look? I tried a couple different formulas in the CF but even the straight-forward ones don't always end in the correct result. Sometimes the count is working and sometimes it isn't.

Any help you can give me would be much appreciated. This is making me nuts lol.

Thanks!

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

03-29-2019 08:23 AM

thanks for your kind words.

I saw your workbook and there are many issues with the CF formulas you set.

Double condition set for a single cell with contradicts each other.

so example in the cell E28 there are two formulas in CF and both of them contradicts each other.

=IF(E28>D28,"true","pass")

=IF(E28<C28,"True","pass")

another issue is that you set IF formulas to return test in CF which is not correct way to set formula.

for example if you want a condition to turn red if certain condition is met then you can simply use equal sign and there is no need for IF function, in fact IF function ruins it.

for example if you want to turn cell E28 to red if its value is greater than D28 then you can simply use =E28>D28 simply as this. CF will return True if condition is met and false if it is not met.

Please see attached your workbook.

I have added two more UDFs so that you can debug and find the problem.

The first UDF return the formula used in the CF for that cell

Function CondFormulaformula(myCell, Optional cond As Long = 1) As String

CondFormulaformula = myCell.FormatConditions(cond).Formula1

End Function

this second formula evaluates the formula used in CF and returns the evaluated value.

Function CondFormula(myCell, Optional cond As Long = 1) As String

CondFormula = Application.Evaluate(myCell.FormatConditions(cond).Formula1)

End Function

you can see in the column H which i highlighted in yellow. the evaluated CF from your formulas and they are not consistent. with the help of two addtional UDFs you can debug your CF formulas and get the correct result.

Hope this helps.

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

04-01-2019 01:53 PM

That worked perfectly! Thank you so very very much! One more question...Is there any way to have the cells that have no data in them remain uncolored? I was playing with the formulas but as I'm sure you've figured out by now I'm not the best at them. Everything else is working beautifully and I am grateful for what I've got but... :)

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

04-09-2019 05:53 AM

You are most welcome.

Yes. you can simply add another condition that cell ="" then select "No color" in the fill

Yes. you can simply add another condition that cell ="" then select "No color" in the fill

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

04-09-2019 08:09 AM

I want to count the number of yellow boxes as in the file below and output in the column "count color yellow"

Thank you so much!

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

04-09-2019 06:24 PM

I found this code to be used in my file but I want to switch from "Sub" to "Function". That is, I want the result of the yellow box displayed in the column count color yellow.

Help me!!

Thank you very much!!

Sub DisplayFormatCount()

'Updateby20150305

Dim Rng As Range

Dim CountRange As Range

Dim ColorRange As Range

Dim xBackColor As Long

Dim xFontColor As Long

On Error Resume Next

xTitleId = "KutoolsforExcel"

Set CountRange = Application.Selection

Set CountRange = Application.InputBox("Count Range :", xTitleId, CountRange.Address, Type: = 8)

Set ColorRange = Application.InputBox("Color Range(single cell):", xTitleId, Type: = 8)

Set ColorRange = ColorRange.Range("A1")

xReturn = 0

For Each Rng In CountRange

qqq = Rng.Value

xxx = Rng.DisplayFormat.Interior.Color

If Rng.DisplayFormat.Interior.Color = ColorRange.DisplayFormat.Interior.Color Then

xBackColor = xBackColor + 1

End If

If Rng.DisplayFormat.Font.Color = ColorRange.DisplayFormat.Font.Color Then

xFontColor = xFontColor + 1

End If

Next

MsgBox "BackColor is " & xBackColor & Chr(10) & "FontColor is " & xFontColor

End Sub

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

04-11-2019 10:44 AM

Hi there! I tried adding the formula E$11="" and format is blank but then the COUNTConditionColorCells formula returns #Value! Any idea what I'm doing wrong?

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

04-12-2019 09:21 AM

try to see if you have already a CF set for that cell with a different formula. then it will work. so you will need to use a workaround. use countblank function instead.

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

04-12-2019 09:22 AM

Hi hiepkt12

you have posted the same question in another thread as well. I have replied to you in there.

you have posted the same question in another thread as well. I have replied to you in there.

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

04-15-2019 09:56 AM

@Jamil Mohammad

Hi Jamil,

Thank you. Your replies to this post have helped me so much!

I used your code and included Application.volatile. But, I can't get my spreadsheet to automatically calculate the number of coloured cells. It will only update the number of cells if I edit the cell in the formula bar. Is there a way I can set up a button on my spreadsheet that will force excel to calculate how many cells have been coloured?

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

04-15-2019 10:44 AM

Hi Sonia1640

Please check that your workbook setting calculation mode is "Automatic"

on the Formula Tab click on "Calculation Option" and from the dropdown plz make sure "Automatic" is selected. If it is Manual then change it to Automatic.

Please check that your workbook setting calculation mode is "Automatic"

on the Formula Tab click on "Calculation Option" and from the dropdown plz make sure "Automatic" is selected. If it is Manual then change it to Automatic.

Highlighted

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

04-15-2019 03:17 PM

Hi Jamil,

The workbook is set to automatic. Do you have any other ideas?

The workbook is set to automatic. Do you have any other ideas?

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

04-15-2019 05:39 PM

Hi Sonia,

Plz check if you have a personal excel file in the Excel start up folder. Also, check if you have any Excel Add-ins enabled.

If the above-mentioned did not work. then place this code below into sheet module not in a regular module.

You can change the range A2:Z100 to the actual range where your formulas are placed.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Me.Range("A2:Z100").Calculate

End Sub

Plz check if you have a personal excel file in the Excel start up folder. Also, check if you have any Excel Add-ins enabled.

If the above-mentioned did not work. then place this code below into sheet module not in a regular module.

You can change the range A2:Z100 to the actual range where your formulas are placed.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Me.Range("A2:Z100").Calculate

End Sub

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

04-20-2019 05:11 PM

@Jamil Mohammad Hi Jamil, I thank you for your work and effort to help.

But, it seems your code is not working here because I need to see conditional formatting changing colors at columns by the values of the cells and I need to count those color changes at rows direction, after that.

Do you think that it can be done?

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

04-30-2019 12:21 AM

Hello,

I have try your VBA formula for sum by conditional formatting color, however formula return "0" result when data are in negative. I did conditionally formatted negative data by using ABS formula. I have staff attendance record which shows short hours by negative sign and excess hours by positive numbers. now I need to sum all short hours and excess hours first by highlighting through conditional format and then sum up those hours.

would you please help me to achieve this.

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

05-06-2019 04:32 PM

I did not see any conditional formatting in your file.

so, I did not understand what were you trying to achieve. If you want to sum the total of cells that are negative then you can use lets say for Column A as an example formula is =SUMIF(A2:A40,"<0") this will sum all values in cells that are negative. but if you want to count cells that have negative values then you can use =COUNTIF(A2:A40,"<0")

and to sum the positive numbers =SUMIF(A2:A40,">0") and to count the positive numbers =COUNTIF(A2:A40,">0")

so, I did not understand what were you trying to achieve. If you want to sum the total of cells that are negative then you can use lets say for Column A as an example formula is =SUMIF(A2:A40,"<0") this will sum all values in cells that are negative. but if you want to count cells that have negative values then you can use =COUNTIF(A2:A40,"<0")

and to sum the positive numbers =SUMIF(A2:A40,">0") and to count the positive numbers =COUNTIF(A2:A40,">0")

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

05-06-2019 08:18 PM

Hi Jamil,

Thank you for response.

Sorry I did not make clear what I'm looking for. I do not want all negative to be sum only those cell value which is greater than -1.5 and less than -10.00 should be colored and sum and similarly all the cell value greater than positive 1.5 are to colored and sum.

I have done conditional formatting in attached file and try to apply your formula but its not working. Please help me out with this.

Thanks again.

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

05-06-2019 08:32 PM

In cell CL2 put this formula and drag down =SUMPRODUCT((ABS(BP2:CK2)>1.5)*(ABS(BP2:CK2<10)))

and in CM2 put this formula and drag down =SUMPRODUCT((--(BP2:CK2>1.5)))

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

05-06-2019 11:09 PM

With this formula it is counting colored cell. For example when I copied the formula to CL2 result showing 22 which is count of color cell (or condition of greater than -1.5 and less than -10). I want sum of all color cell.

Thank you.

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

05-15-2019 09:05 AM

I am so VERY impressed with your expertise in MS Excel. I'm trying to do this very same task of counting cells based on CF color. I've attempted your code but get "NO-COLOR" as the output. I've attached a dummy file so you can see what's going on.

The only thing I can figure is that it's a different version of excel or I have a setting blocked somewhere.

Thank you so much for your kind consideration and help.

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

05-15-2019 10:54 AM

if you want to sum instead of count then use =SUMPRODUCT((ABS(BP2:CK2)>1.5)*(ABS(BP2:CK2<10)),BP2:CK2)

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

05-15-2019 10:58 AM

Thanks for your kind words.

I looked at the file you attached.

If you read my earlier posts, I have mentioned to other users with similar issues as stated below

- Your CF range does not use only formula, for UDF to work, CF needs to be set up only using formula and not built-in options given for example you use the option of "Format cells that contain"
- UDF range uses the whole A to M columns, whereas your CF ranges are completely different. for example some of them starts from row one and some in row 2 , another CF uses double column applied range $H$2:$H$31,$J$2:$J$31
- For UDF to work, the range in UDF and ranges in CF must match and the CFs needs to be set using only formula.

Hope this helps.

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

05-17-2019 12:10 AM - edited 05-17-2019 12:12 AM

Hi Mr Jamil

I would like to thank you for this formula. It works great! However, I have a query. Please see attached file. I have a set of KPI’s for disciplines to meet. When I use your formula to count, ‘No of disciplines met by KPI’, it works. However, when I used your formula to count, ‘No of KPI's met by Discipline’, it doesn’t work. May I know what could be wrong? Your help is greatly appreciated :)

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

05-20-2019 03:01 AM

Hi Jamil,

Thanks for sharing the code for counting colored cells in a conditionally formatted sheet. Just a quick question, "Can you please help me alter the code to make it count the colored cells in a specific row. This current code is default for range starting from cell A3. Say suppose I want to count the colored cells for range A4 to G4 in your sheet 'COUNTBYCOLOR.xls, it does not give me correct result."

Thanks

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

05-20-2019 05:16 PM

It is because your CF is set C3:G3 and not C3:C5

so only C3:G3 works because your CF has this range.

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

05-20-2019 05:18 PM

if you want to count by row then your conditional formatting applied range should be also by that row and now the entire range. so if you want it to work on A4 to G4 then set up the conditional formatting range to be applied only on A4:G4

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

06-09-2019 11:07 PM

Hi There

I know very little about VBA's.

I've got a very large s/sheet with data that I've just inherited. I need to add this months data to the master sheet (hystoric) and I'm trying to make the process easier and less time consuming (previously done manually). It's got columns with Customer Name, Address, Territory, Serial Number, Device Name, etc (columns A to O) and then approx 6900 rows of data. In my Serial Number column I've got a conditional format (column H) (format values where this formula is true =countif(if serial number is different to last month) - fill - red) and then I've got a conditional format in column K (format values where device name is different to last month - fill - blue). I've also got subtotals - count per territory number.

What I'm trying to achieve is to count all the non-coloured Serial Numbers (cells in H) and all the change in device name (non-coloured cells in K (less non-coloured cells in H)).

I've inserted your UDF in VBA. In H373 (first change in territory) I've typed =COUNTConditionColorCells and hit Enter and get the "#Value!" error. Not sure what I'm doing wrong, please help.

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

06-11-2019 02:00 PM

I'm trying this formula and I keep getting 0's. I'm not sure what I'm doing wrong and I'm wondering if you can help. See my data.

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

06-19-2019 04:10 AM

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

06-19-2019 04:18 AM

If you read my earlier posts, you will see that I mentioned UDF only works if CF is set using formula not build it conditions.

I changed the CF in the attached file as an example and see it works.

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

06-27-2019 07:56 AM

Hi Jamil, trying to use the count feature on conditional formatted cells, but the count is not accurate. My range of cells are restricted to 1 row at a time of cell highlighted in blue, but it appears to give the same result, regardless of the range provided. The spreadsheet has been attached. Any thought?

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

07-01-2019 12:06 PM

Hi @Jamil Mohammad ,

I have a similar question, however, when I applied the codes it only counts the cell by column, which I also need it to be counted by row. I wonder if it is because each column has a different condition formatting criteria. Could you please help? Please see the attached file.

Thanks!!

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

07-01-2019 01:15 PM

@Deleted

Hi,

Your CF range was vertical while the formula range was horizontal. so i changed your CF range to horizontal and it works now. see attached.

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

07-01-2019 01:17 PM

Hi, please read my earlier posts. the UDF do not work if the CF is set using built-in conditions. UDF works only when you set conditions with formula. "Use a formula to determine which cell to format"

if you set your CF using formula, then it works.

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

07-07-2019 03:26 PM

Hi could you please check my file, i can't make it work, it gives me the error:

#VALOR!

English is not my first language, so i tried to make it work reading the posts here. Thanks if you can take a look into my file.

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

07-17-2019 11:44 AM - edited 07-17-2019 11:47 AM

Hi, I try your code and i still cant count color in my workbook. keep saying no colour, eventough its already the same color

can you take a look at my file? really appreciate it

is it because I am using excel 2013?

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

07-18-2019 11:17 AM

Hi

your conditional formatting range and the range used inside the UDF are not the same, that is why it does not work. please read my earlier comments to other users with similar issue.

your conditional formatting range and the range used inside the UDF are not the same, that is why it does not work. please read my earlier comments to other users with similar issue.

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

07-19-2019 09:50 AM

Hi Jamil,

I've been using your UDF for counting conditional cells and it has been working great. I was wondering if there is a way to modify the code so that when I filter my data in a table, the totals will update automatically without counting the hidden rows.

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

07-29-2019 10:19 AM

Hi Robin,

It is maybe possible to do that, but I do not have time to write the code. I suggest you open a new question in the forum.

It is maybe possible to do that, but I do not have time to write the code. I suggest you open a new question in the forum.

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

09-02-2019 12:23 AM

I have a rather large excel file with 480 columns and 1451 rows. I currently have conditional formatting set up to find - and color - the largest value in each row. What I need to know is the count of row maximums in each column. I see that your solution only works when the count function and conditional formatting are applied to the same range. I already know how many I have per row - 1. Is there any way to count conditional formatting when the ranges don't match?

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

09-04-2019 08:10 AM

I could not understand your question.

If you are trying to count number of cells when condition is not matched, then you simply sort your data by color of "none" and then count.

If you are trying to count number of cells when condition is not matched, then you simply sort your data by color of "none" and then count.

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

09-05-2019 02:47 PM

No, I'm trying to count when the condition is met, however, my conditional formatting runs across rows. I need to count instances when conditional formatting is matched within columns. As you had previously mentioned to someone else, your tool for counting conditional formatting only works when the range and the area being counted match. My question is: is there a workaround for when the conditional formattting range and the range being summed do not match?

FYI the same condition is being tested for within each row for each row within the summed columns.

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

09-05-2019 02:51 PM - edited 09-05-2019 03:20 PM

I know it is possible to sort/filter via color and I could do that - and manually count - for each of my 480 columns; but since my data will change frequently over the course of the project, I'd prefer not to manually count 480 columns dozens, possibly hundreds, of times. I would much prefer to have a readout listed for each column.

Related Conversations

Stable version of Edge insider browser

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

6 Likes

35 Replies

flashing a white screen while open new tab

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

14 Likes

14 Replies

How to Prevent Teams from Auto-Launch

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

8 Likes

29 Replies

Security Community Webinars

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

11 Likes

13 Replies

Share

Popular

Learning Resources

Programs

Values

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