Conditional Formatting

H

H

I am wondering if there is a way to highlight in Conditional Formatting when
a Customer 1st hits 1M mark

My data is in a pivot table that looks like


Customer Name Yr2000Qtr1 Yr2000Qtr2 etc .. on up to
Yr2010Qtr1
Customer A $1,000,000 $50,000
Customer B $90,000 $1,500,000 $2,000,000

I would want to highlight Customer A and the amt in YR2000Qtr1
and Customer B Yr2000Qtr2 amt but not the next one (Only the 1st
time they hit it.
 
L

Luke M

format-Conditional format.
Formula is:
=AND(B2>=1000000,COUNTIF($A2:A2,">=1000000")=0)

Choose desired format.
Copy formatting across and down as needed.
 
H

H

Thank you, I tried
=AND(B6>=1000000,COUNTIF($A3:A3,">=1000000")=0)

but it's still highlighting the other columns when it goes over a $1m and
I'm hoping for it to only highlight the 1st time it goes over?
 
L

Luke M

All parts of the formula need to refer to the same row. So, if you are
formatting cell B6, formula is:

=AND(B6>=1000000,COUNTIF($A6:A6,">=1000000")=0)

or, for clarity:
=AND(B6>=1000000,COUNTIF($B6:B6,">=1000000")=1)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top