Conditional Formatting using Countifs - help
Closed
prady3011
Posts
3
Registration date
Tuesday November 4, 2014
Status
Member
Last seen
November 4, 2014
-
Nov 4, 2014 at 05:42 AM
Mazzaropi Posts 1980 Registration date Monday August 16, 2010 Status Contributor Last seen December 22, 2022 - Nov 5, 2014 at 12:20 PM
Mazzaropi Posts 1980 Registration date Monday August 16, 2010 Status Contributor Last seen December 22, 2022 - Nov 5, 2014 at 12:20 PM
Related:
- Conditional Formatting using Countifs - help
- Excel conditional formatting if another cell contains specific text ✓ - Excel Forum
- Conditional formatting if another cell contains any text ✓ - Excel Forum
- Conditional format based on another cell containing text - Excel Forum
- Excel conditional formatting if cell contains multiple specific text ✓ - Excel Forum
- Conditional formatting if another cell is blank - Excel Forum
5 replies
Mazzaropi
Posts
1980
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
December 22, 2022
146
Nov 4, 2014 at 06:57 AM
Nov 4, 2014 at 06:57 AM
prady3011, Good morning.
I'm not sure if I understood well your necessity.
Try to use:
=AND(COUNTIFS($D$3:$D$21,"Yes",$A$3:$A$21,$A$3)>1 , $A3="YES")
Is it what you want?
I hope it helps.
I'm not sure if I understood well your necessity.
Try to use:
=AND(COUNTIFS($D$3:$D$21,"Yes",$A$3:$A$21,$A$3)>1 , $A3="YES")
Is it what you want?
I hope it helps.
prady3011
Posts
3
Registration date
Tuesday November 4, 2014
Status
Member
Last seen
November 4, 2014
Nov 4, 2014 at 07:20 AM
Nov 4, 2014 at 07:20 AM
Hi Mazzaropi,
I've got the following data in column A and column D. I want to conditionally format Column D, where the value in Column A is 'a' and the value in Column D is 'Yes'. However, with my current formula, mentioned above, Column D is being formatted even when the value in it is 'No' (see last line of the the data)
So, what I want to do is: the cells to be highlighted for every value in column A, where Column D is a 'Yes' and this combination occurs more than once.
In example below: I want the following cells to be highlighted where A = a and D =Yes and it occurs more than once. In the example, the combination occurs twice - and I want to conditionally format it, but with my formula even the cell where A = a and D = No is being highlighted.
Your formula, doesn't highlight anything.
A D
a Yes
b 0
c 0
v 0
Music Team
a Yes
f 0
v 0
c 0
YPS Team
a1 0
b1 0
c1 0
d1 0
Communion Team
a No
I've got the following data in column A and column D. I want to conditionally format Column D, where the value in Column A is 'a' and the value in Column D is 'Yes'. However, with my current formula, mentioned above, Column D is being formatted even when the value in it is 'No' (see last line of the the data)
So, what I want to do is: the cells to be highlighted for every value in column A, where Column D is a 'Yes' and this combination occurs more than once.
In example below: I want the following cells to be highlighted where A = a and D =Yes and it occurs more than once. In the example, the combination occurs twice - and I want to conditionally format it, but with my formula even the cell where A = a and D = No is being highlighted.
Your formula, doesn't highlight anything.
A D
a Yes
b 0
c 0
v 0
Music Team
a Yes
f 0
v 0
c 0
YPS Team
a1 0
b1 0
c1 0
d1 0
Communion Team
a No
Mazzaropi
Posts
1980
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
December 22, 2022
146
Nov 4, 2014 at 10:05 AM
Nov 4, 2014 at 10:05 AM
prady3011, Good afternoon.
I did an Excel file to show you the formula I mentioned before using your data as example.
Please, take a look at it.
http://speedy.sh/3WYfz/04-11-2014-en-Kioskea-Conditional-Formatting-OK.xlsx
Is it what you want?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
I did an Excel file to show you the formula I mentioned before using your data as example.
Please, take a look at it.
http://speedy.sh/3WYfz/04-11-2014-en-Kioskea-Conditional-Formatting-OK.xlsx
Is it what you want?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
prady3011
Posts
3
Registration date
Tuesday November 4, 2014
Status
Member
Last seen
November 4, 2014
Nov 4, 2014 at 11:18 AM
Nov 4, 2014 at 11:18 AM
Hi Marcilio,
You're attachment was quite helpful, but not exactly what I want. I've attached an image below. You're formula works only when the value in column A is 'a', but I want it to apply to value 'c' , etc.
I want the cells to be highlighted only for the following combinations:
If the value is Yes, for any value in column A and if the value in Column A repeats more than once, the cell should be hightlighted. In my image, I don't want 'No' to be highlighted.
Thanks for your help so far.

You're attachment was quite helpful, but not exactly what I want. I've attached an image below. You're formula works only when the value in column A is 'a', but I want it to apply to value 'c' , etc.
I want the cells to be highlighted only for the following combinations:
If the value is Yes, for any value in column A and if the value in Column A repeats more than once, the cell should be hightlighted. In my image, I don't want 'No' to be highlighted.
Thanks for your help so far.

Didn't find the answer you are looking for?
Ask a question
Mazzaropi
Posts
1980
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
December 22, 2022
146
Nov 5, 2014 at 12:20 PM
Nov 5, 2014 at 12:20 PM
prady3011, Good afternoon.
"...If the value is Yes, for any value in column A and if the value in Column A repeats more than once, the cell should be hightlighted. In my image, I don't want 'No' to be highlighted..."
Take a look at it:
http://speedy.sh/bcm2E/04-11-2014-en-Kioskea-Conditional-Formatting-OK.xlsx
Please, tell us if it worked for you.
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
"...If the value is Yes, for any value in column A and if the value in Column A repeats more than once, the cell should be hightlighted. In my image, I don't want 'No' to be highlighted..."
Take a look at it:
http://speedy.sh/bcm2E/04-11-2014-en-Kioskea-Conditional-Formatting-OK.xlsx
Please, tell us if it worked for you.
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão