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 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 - Nov 5, 2014 at 12:20 PM
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 - Nov 5, 2014 at 12:20 PM
=COUNTIFS(D$3:D$21,"Yes",$A$3:$A$21,$A3)>1
Above is my countif statement to count the number of times a particular criteria exists.
D3:D21 should be 'Yes' and A3:A21 should contain the value in A3. The conditional formatting works for A3 and A9 where D3 and D9 is "Yes". However, in A18, when the value is same as the value in A3 and D18 is "No", the field is still formatted as above.
How do I get it to format only if D3:D21 is a 'Yes' and the value ins A3:A21 is the same as value in A3?
Above is my countif statement to count the number of times a particular criteria exists.
D3:D21 should be 'Yes' and A3:A21 should contain the value in A3. The conditional formatting works for A3 and A9 where D3 and D9 is "Yes". However, in A18, when the value is same as the value in A3 and D18 is "No", the field is still formatted as above.
How do I get it to format only if D3:D21 is a 'Yes' and the value ins A3:A21 is the same as value in A3?
Related:
- Conditional Formatting using Countifs - help
- How to clear formatting in excel - Guide
- Phone formatting software for pc - Download - File management
- Formatting usb mac - Guide
- Code for formatting android phone - Guide
- Excel conditional formatting based on date - Guide
5 responses
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
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
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
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.

Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
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