98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Combining COUNTIF and LEFT Functions in Conditional Formatting
Combining COUNTIF and LEFT Functions in Conditional Formatting
Resolved · High Priority · Version 365
Jonathan has attended:
Office 365 End User course
Excel Intermediate course
Excel Advanced course
Combining COUNTIF and LEFT Functions in Conditional Formatting
Hi. Essentially in my Excel sheet I want to create a border under cells in a column that share the same 2 first characters, but I do not know what those characters will be ahead of time and information will be added to the spreadsheet after the fact (so I CANNOT specify phrases).
I have used the COUNTIF function to do this bordering function under Conditional Formatting before and I expect the LEFT function is the solution to counting characters, but I cannot work out how to combine the formulas without knowing the specific information ahead of time.
This is my COUNTIF formula so far, would I add the character specification at the end, so instead of any one cell different it's identifying the initial different characters? =COUNTIF($D$2:$D2,$D2)=1
Any advice?
RE: Combining COUNTIF and LEFT Functions in Conditional Formatti
Hi Jonathan,
Thank you for the forum question.
Try:
=COUNTIF($D$2:$D$10,LEFT(D2,2)&"*")>1
You will need to change the range to the actual range. Here the criteria range is $D$2:$D$10.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Combining COUNTIF and LEFT Functions in Conditional Formatti
Hi Jens,
Thank you very much for your help!
Your solution was just a couple characters shy of solving my problem! Appears I needed another $ in front of the LEFT equation and = instead >.
=COUNTIF($D$2:$D2,LEFT($D2,2)&"*")=1
Apart from some minor bugs that I think are due to my unique data set, this is now resolved.
Thanks again.
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Jumping Between Sheets in a BookPgDn and PgUp keys scrolls up and down a screen page in most applications. |