Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Conditional Formatting using Countifs - help [Closed]

Report
Posts
3
Registration date
Tuesday November 4, 2014
Status
Member
Last seen
November 4, 2014
 -
Posts
1852
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
November 7, 2020
 -
=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?
See more

5 replies

Answer 1 / 5
Posts
1852
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
November 7, 2020
 136
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.
Answer 2 / 5
Posts
3
Registration date
Tuesday November 4, 2014
Status
Member
Last seen
November 4, 2014
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
Answer 3 / 5
Posts
1852
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
November 7, 2020
 136
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
Answer 4 / 5
Posts
3
Registration date
Tuesday November 4, 2014
Status
Member
Last seen
November 4, 2014
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.



Answer 5 / 5
Posts
1852
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
November 7, 2020
 136
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
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!