combining countif and left

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

• Home
• Courses
• Promotions
• Schedule
• Formats
• Our Clients

Forum home » Delegate support and help forum » » Combining COUNTIF and LEFT Functions in Conditional Formatting

# Combining COUNTIF and LEFT Functions in Conditional Formatting

Resolved · High Priority · Version 365

Edited on Fri 19 May 2023, 12:12

Jonathan has attended:
Office 365 End User course
Excel Intermediate 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

### 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 courses

Training information:

 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 What does 'Resolved' mean? Any suggestions, questions or comments? Please post in the Improve the forum thread.

### Jumping Between Sheets in a Book

PgDn and PgUp keys scrolls up and down a screen page in most applications.

Ctrl+PgDn and Ctrl+PgUp keys jump from one sheet in your workbook to the next, up or down through the pages.

View all Excel hints and tips