| Excel nerds... 00:03 - Aug 24 with 4727 views | sparks |
The wife has a question for the twtd intelligentsia.... I don't claim to understand it.
"I need help doing a count of customers based on a conditional formatting. I.e. how many customers are in the 'yellow' cells in a column. I've tried =CountCcolor(x:y,z) but the result comes back as zero. I ran the module on the VBA. There is Zutool but I'm not sure I can install at work.
There was a background colour but I need the conditional formatting to say where the info is coming from.
Any ideas please?
Thanks"
|  |
| The presence of those seeking the truth is infinitely to be preferred to the presence of those who think they've found it.
(Sir Terry Pratchett) | | Poll: | Is Fred drunk this morning? |
| |  |
| Excel nerds... on 00:35 - Aug 24 with 4682 views | Cheshire_Tractor |
What do the yellow cells indicate?
I ask because it may be easier to have a status column. On one of my spreadsheets I have thousands of invoices each with a formula to decide its status (eg overdue, paid, pending etc) I use a countif formula to give my totals for each.
I then use the value in the column to dictate the conditional formatting rather than the other way around by trying to count the yellows using VBA [Post edited 24 Aug 2017 0:46]
|  | | |
| Excel nerds... on 08:04 - Aug 24 with 4544 views | TractorWood |
I remember trying to once do something similar from conditional formatting. I don't think it's possible as conditional formatting doesn't formally record cell colour, just changes it's appearance. I think I just put some formulae around the parameters I was trying to establish and cracked on. There's usually a way round stuff. |  |
| |
| Excel nerds... on 08:10 - Aug 24 with 4528 views | Bluefish |
From my excel advanced days (quite a while ago) you could do this on cell colour. The obvious way round it was some sort of numbering system if possible showing where they came from |  |
| |
| Excel nerds... on 08:44 - Aug 24 with 4488 views | JakeITFC |
Not explicitly, but you obviously have the rules in the conditional formatting setup so you could just build a COUNTIF around those.
You'd have to write your own function in VBA to do it anyway, so may as well just do it with COUNTIF. |  | | |
| Excel nerds... on 08:54 - Aug 24 with 4471 views | AbBlue |
Hi,
Think I have something here that should do the job...
The first sub finds the ColorIndex of the active cell that can then be plugged in to the second sub, setting the CI value.
Result returns in a pop up, but should not be an issue to output count value to a cell.
Shout if having issues.
Sub FindColour()
CI = ActiveCell.DisplayFormat.Interior.ColorIndex
MsgBox "The Colour Index (CI) of the Active Cell is " & CI
End Sub
Sub CountColourx()
ColNum = 4 'Column number, column A = 1...
MinRow = 5 'Starting row of range, min = 1
MaxRow = 17 'End row of range, max = 1048576. Use min and max for whole column
CI = 6 'ColorIndex
C = 0 'Count
For Row = MinRow To MaxRow
Cells(Row, ColNum).Activate
If ActiveCell.DisplayFormat.Interior.ColorIndex = CI Then
C = C + 1
End If
Next Row
MsgBox "There are " & C & " rows matching selected colour index"
End Sub [Post edited 24 Aug 2017 8:56]
|  | | |
| Excel nerds... on 09:07 - Aug 24 with 4444 views | Kropotkin123 |
If the cells respond correctly to the conditional formatting, then there must be a working formula already there. This suggests that the solution should be relatively easy in an array formula. I can have a look tonight, if you send me the original document, via private message. |  |
| |
| Excel nerds... on 09:19 - Aug 24 with 4418 views | sparks |
| Excel nerds... on 09:07 - Aug 24 by Kropotkin123 |
If the cells respond correctly to the conditional formatting, then there must be a working formula already there. This suggests that the solution should be relatively easy in an array formula. I can have a look tonight, if you send me the original document, via private message. |
Thanks folks. Will direct her to your comments.
It's a long time since I was good at excel... | |
| The presence of those seeking the truth is infinitely to be preferred to the presence of those who think they've found it.
(Sir Terry Pratchett) | | Poll: | Is Fred drunk this morning? |
| |
| Excel nerds... on 09:25 - Aug 24 with 4404 views | Jon_456 |
I assume you already have some form of conditional formatting which turns the cell colour yellow if that cell is of a certain value.
It might not be the most efficient way but the way I would do this would be to add another column and create the same rule which turns the cell yellow, but in this column make it = 1. You can then add the numbers of this column up to give you that total number, and then hide this column so it doesn't show on the spread sheet.
For example if cell B2 has the format which turns yellow if of a certain value, make cell C2 have the same format but = 1 rather than turning yellow.
Like I said there may be a much easier way but this is the best way I know of. |  | | |
Login to get fewer ads
| Excel nerds... on 09:54 - Aug 24 with 4366 views | Kropotkin123 |
| Excel nerds... on 09:25 - Aug 24 by Jon_456 |
I assume you already have some form of conditional formatting which turns the cell colour yellow if that cell is of a certain value.
It might not be the most efficient way but the way I would do this would be to add another column and create the same rule which turns the cell yellow, but in this column make it = 1. You can then add the numbers of this column up to give you that total number, and then hide this column so it doesn't show on the spread sheet.
For example if cell B2 has the format which turns yellow if of a certain value, make cell C2 have the same format but = 1 rather than turning yellow.
Like I said there may be a much easier way but this is the best way I know of. |
The easier way of doing that would be COUNTIF. Or if it is multiple functions like that COUNTIFS, or COUNTIF combined with AND and OR. Or if it is more difficult, an array formula with IF or MATCH. Even SUMPRODUCT combos could get around creating a new column, but it depends on the formulas dictating the conditional formatting.
I guess I fit into the nerd bracket, as I keep checking back every 5mins, to see if we have more info on it. | |
| |
| Excel nerds... on 10:26 - Aug 24 with 4333 views | AbBlue |
| Excel nerds... on 09:54 - Aug 24 by Kropotkin123 |
The easier way of doing that would be COUNTIF. Or if it is multiple functions like that COUNTIFS, or COUNTIF combined with AND and OR. Or if it is more difficult, an array formula with IF or MATCH. Even SUMPRODUCT combos could get around creating a new column, but it depends on the formulas dictating the conditional formatting.
I guess I fit into the nerd bracket, as I keep checking back every 5mins, to see if we have more info on it. |
The reason I posted the macros above is I do not think there is a way of returning the colour with a formula in the worksheet unless you have created a function to allow you to do so.
So unless the logic of the conditional formatting (if that's what's being used) can be changed to output a value, I think macros are the only way. If conditional formatting is not being used, then I am fairly certain you will need a macro. Although happy to be proven wrong as would be a useful tool.
Only other thought if conditional formatting is being used is could a pivot table be utilised? Could get the data shown in the pivot to whatever level of detail that allows you to apply the same conditional formatting, and either the sum of the data or sub-total in the report will give you the count. | | | |
| Excel nerds... on 10:57 - Aug 24 with 4297 views | Kropotkin123 |
| Excel nerds... on 10:26 - Aug 24 by AbBlue |
The reason I posted the macros above is I do not think there is a way of returning the colour with a formula in the worksheet unless you have created a function to allow you to do so.
So unless the logic of the conditional formatting (if that's what's being used) can be changed to output a value, I think macros are the only way. If conditional formatting is not being used, then I am fairly certain you will need a macro. Although happy to be proven wrong as would be a useful tool.
Only other thought if conditional formatting is being used is could a pivot table be utilised? Could get the data shown in the pivot to whatever level of detail that allows you to apply the same conditional formatting, and either the sum of the data or sub-total in the report will give you the count. |
Yes, you are correct, it's never based on the colour itself, just the underlying formulas. It would be simpler if MS Excel just did a countcolour formula, but I guess they haven't as you can just do a macros, as you have, or use the underlying formula.
I guess they aren't comfortable using pivot tables, but maybe it is something else we haven't anticipated. Until we have the raw data it is just speculation on our part with regards to formulas and pivot tables. | |
| |
| Excel nerds... on 11:13 - Aug 24 with 4278 views | Cheshire_Tractor |
Not that there is anything wrong with the macros it just seems like it's using a sledgehammer to crack a nut. Surely it's simpler.
There is clearly a formula calculating the conditions to turn it yellow in the conditional formatting.
Just put that in a formula in a column, call the output "yellow" if you like. Hide it if you like.
Then do the conditional formatting telling it to colour the row yellow if that cell says yellow (or whatever you want to call the category you are highlighting that colour). And use countif to add them up.
As I said earlier I have exactly the same scenario on a list of invoices. It counts them up and colours the whole row no problem.
Also happy to take a look if you PM to me. | | | |
| Excel nerds... on 12:19 - Aug 24 with 4228 views | Kropotkin123 |
| Excel nerds... on 11:13 - Aug 24 by Cheshire_Tractor |
Not that there is anything wrong with the macros it just seems like it's using a sledgehammer to crack a nut. Surely it's simpler.
There is clearly a formula calculating the conditions to turn it yellow in the conditional formatting.
Just put that in a formula in a column, call the output "yellow" if you like. Hide it if you like.
Then do the conditional formatting telling it to colour the row yellow if that cell says yellow (or whatever you want to call the category you are highlighting that colour). And use countif to add them up.
As I said earlier I have exactly the same scenario on a list of invoices. It counts them up and colours the whole row no problem.
Also happy to take a look if you PM to me. |
But why create an additional column, when you don't need to? Surely a macros is more robust than creating and hiding a column? Also seems needlessly convoluted to be making new columns and hiding them, when an array function can be as detailed as you want.
Let's say you have these age ranges in a1 to a20
35,23,45,29,20,26,24,26,84,76,34,23,56,27,26,46,64,36,43,45
Count 36 and 45
{=SUM(IF((A1:A20=36)+(A1:A20=45),1,0))}
3
Count equal or less than 20 and equal or greater than 60
{=SUM(IF((A1:A20<=20)+(A1:A20>=60),1,0))}
4
equal or less than c1 and equal or greater than c2
{=SUM(IF((A1:A20<=C1)+(A1:A20>=C2),1,0))}
2
Make it as complicated and dynamic as you wish. | |
| |
| Excel nerds... on 12:38 - Aug 24 with 4206 views | Cheshire_Tractor |
| Excel nerds... on 12:19 - Aug 24 by Kropotkin123 |
But why create an additional column, when you don't need to? Surely a macros is more robust than creating and hiding a column? Also seems needlessly convoluted to be making new columns and hiding them, when an array function can be as detailed as you want.
Let's say you have these age ranges in a1 to a20
35,23,45,29,20,26,24,26,84,76,34,23,56,27,26,46,64,36,43,45
Count 36 and 45
{=SUM(IF((A1:A20=36)+(A1:A20=45),1,0))}
3
Count equal or less than 20 and equal or greater than 60
{=SUM(IF((A1:A20<=20)+(A1:A20>=60),1,0))}
4
equal or less than c1 and equal or greater than c2
{=SUM(IF((A1:A20<=C1)+(A1:A20>=C2),1,0))}
2
Make it as complicated and dynamic as you wish. |
"Why create another column?" Cos it's quick + easy .
Personally I wouldn't hide the column as I tend to use the value as the basis of other analysis elsewhere. I only suggested hiding it (or grouping it) if you really didn't want to see it.
I see what you are doing with the array formulae but out of interest how would that add up the number of yellow cells? I don't think counting cells with values was the issue. | | | |
| Excel nerds... on 12:41 - Aug 24 with 4199 views | JakeITFC |
| Excel nerds... on 12:38 - Aug 24 by Cheshire_Tractor |
"Why create another column?" Cos it's quick + easy .
Personally I wouldn't hide the column as I tend to use the value as the basis of other analysis elsewhere. I only suggested hiding it (or grouping it) if you really didn't want to see it.
I see what you are doing with the array formulae but out of interest how would that add up the number of yellow cells? I don't think counting cells with values was the issue. |
It's not counting the yellow, that's the point.
It's doing your additional column calc in one formula. | | | |
| Excel nerds... on 12:50 - Aug 24 with 4173 views | Kropotkin123 |
| Excel nerds... on 12:41 - Aug 24 by JakeITFC |
It's not counting the yellow, that's the point.
It's doing your additional column calc in one formula. |
exactly! | |
| |
| Excel nerds... on 13:03 - Aug 24 with 4151 views | Cheshire_Tractor |
| Excel nerds... on 12:41 - Aug 24 by JakeITFC |
It's not counting the yellow, that's the point.
It's doing your additional column calc in one formula. |
Not being funny, I'm genuinely interested as the point of my additional column would be so you could easily count the yellow rows.
If the array formulae doesn't do that then what advantage does it bring?
I thought the whole point was to count the yellow rows | | | |
| Excel nerds... on 13:10 - Aug 24 with 4128 views | JakeITFC |
| Excel nerds... on 13:03 - Aug 24 by Cheshire_Tractor |
Not being funny, I'm genuinely interested as the point of my additional column would be so you could easily count the yellow rows.
If the array formulae doesn't do that then what advantage does it bring?
I thought the whole point was to count the yellow rows |
They are yellow for a reason (the condition in the conditional formatting).
The array method detailed above has taken the conditions and applied a count of any of the cells that meet those conditions (which will coincidentally be yellow).
There's nothing wrong with adding an additional column, it will get you the answer you require, but it's a much neater solution to get it done and leave the dataset as is. | | | |
| Excel nerds... on 13:20 - Aug 24 with 4104 views | Cheshire_Tractor |
| Excel nerds... on 13:10 - Aug 24 by JakeITFC |
They are yellow for a reason (the condition in the conditional formatting).
The array method detailed above has taken the conditions and applied a count of any of the cells that meet those conditions (which will coincidentally be yellow).
There's nothing wrong with adding an additional column, it will get you the answer you require, but it's a much neater solution to get it done and leave the dataset as is. |
Right so it would count the yellows by counting the ones with matching conditions to those used in the conditional formula, (rather than counting the colours)
Nifty that.
All goes to show more than one way around the problem. | | | |
| Excel nerds... on 13:23 - Aug 24 with 4096 views | AbBlue |
| Excel nerds... on 13:20 - Aug 24 by Cheshire_Tractor |
Right so it would count the yellows by counting the ones with matching conditions to those used in the conditional formula, (rather than counting the colours)
Nifty that.
All goes to show more than one way around the problem. |
However, all dead in the water if the yellow formatting is not applied conditionally.
Do we know the answer to that yet? | | | |
| Excel nerds... on 13:26 - Aug 24 with 4084 views | sparks |
| Excel nerds... on 13:23 - Aug 24 by AbBlue |
However, all dead in the water if the yellow formatting is not applied conditionally.
Do we know the answer to that yet? |
All beyond me. Will ask her when I get home! ! | |
| The presence of those seeking the truth is infinitely to be preferred to the presence of those who think they've found it.
(Sir Terry Pratchett) | | Poll: | Is Fred drunk this morning? |
| |
| Excel nerds... on 13:46 - Aug 24 with 4055 views | RamRob |
| Excel nerds... on 13:26 - Aug 24 by sparks |
All beyond me. Will ask her when I get home! ! |
Just sort by colour and select the yellow cells, easy [Post edited 24 Aug 2017 13:47]
|  |
| |
| Excel nerds... on 14:07 - Aug 24 with 4038 views | artsbossbeard |
If you point the phasers at the diliphilium crystals, we might be able to get away on impulse power. |  |
| Please note: prior to hitting the post button, I've double checked for anything that could be construed as "Anti Semitic" and to the best of my knowledge it isn't. Anything deemed to be of a Xenophobic nature is therefore purely accidental or down to your own misconstruing. | | Poll: | Raining in IP8 - shall I get the washing in? |
| |
| |
By continuing to use the site, you agree to our use of cookies and to abide by our Terms and Conditions.
