Previous Topic Next Topic

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
=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?

5 replies

Answer 1 / 5
Mazzaropi Posts 1980 Registration date Monday August 16, 2010 Status Contributor Last seen December 22, 2022 146
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.
0
Answer 2 / 5
prady3011 Posts 3 Registration date Tuesday November 4, 2014 Status Member Last seen November 4, 2014
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
0
Answer 3 / 5
Mazzaropi Posts 1980 Registration date Monday August 16, 2010 Status Contributor Last seen December 22, 2022 146
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
0
Answer 4 / 5
prady3011 Posts 3 Registration date Tuesday November 4, 2014 Status Member Last seen November 4, 2014
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.



0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 5
Mazzaropi Posts 1980 Registration date Monday August 16, 2010 Status Contributor Last seen December 22, 2022 146
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
0