Contextures News 20191210

Excel COUNTIF Problems

December 10, 2019

Block duplicate entries, COUNTIF problems, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Happy Holidays! This is the final newsletter for 2019, and I'll be back on January 7th.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Block Duplicates

In some workbooks, you might need to block duplicate entries in a column. For example, we don't want 2 employees to have the same ID number. See how to set up a custom rule for that, with data validation. And keep reading, to see why COUNTIF can cause problems for you.

First, here's how to block duplicate entries:

  • Select the cells where you want the rule applied -- A2:A3 in the screen shot below. (That range is named EmpIDs)
  • On the Data tab, click Data Validation, and for Allow, select Custom
  • In the Formula box, type this formula that checks for matching entries.:
    • =COUNTIF(EmpIDs,A2) <= 1
  • COUNTIF counts any matches in the EmpIDs range, for the number in cell A2. If there's more than one, the entry will be blocked

formula to block duplicates

COUNTIF Problems: If your numbers are text, instead of real numbers, COUNTIF will cause problems. In the table below, you can see that COUNTIF counts "0123" and "123" as (false) duplicates. That's because it treats text numbers like real numbers, so "0123" =123 and "123" =123.

Instead, use SUMPRODUCT. It treats text numbers as text, and "0123" is different from "123"

block duplicates for text numbers

Here's the data validation formula to use, if you have "text" numbers:

=SUMPRODUCT(- -(EmpIDs=A2)) <=1

See more examples of data validation custom rules on my Contextures site. There's a video too, and a sample file to download.

On Contextures

Here are a couple of things from my Contextures site and blog.

Holiday Planner: Whether or not you're planning any holiday events this year, go to the Holiday Planner page on my Contextures site, and get the sample file. You can use the techniques in business projects too. For example, enter event dates and times on one sheet, and see them in a calendar layout on a different sheet. The file has a few simple macros, and lots of formulas.

holiday planner

Christmas Tree: Here's another holiday-themed Excel workbook, with ideas that you could adapt for business projects. It shows a message that changes based on a VLOOKUP formula, conditional formatting shows hidden features when a target number is reached, and named ranges make it easy to control what's happening. There are no macros in the workbook

Christmas tree workbook

Excel Articles

Here are a few Excel-related articles that you might find useful or interesting.

Macros: If you want to learn more about Excel macros, Jon Acampora is running free webinars this week that show you how to get started with Excel macros, and save time on Excel tasks. There's a full course too - VBA Pro - if you decide to learn even more about Excel programming. (Level-Int/Adv)

No Macros: There's a daily coding challenge on the Advent of Code site - click on a number to see that day's challenge. Aila Albrecht isn't a programmer, so she tries to solve the problems using Excel. If you'd like a challenge, try one yourself! (Level-Int/Adv)

Power Query: If things are slow at your office over the holidays, invest some time to learn more about Power Query. Mike Girvin has lots of free PQ videos on his YouTube channel (ExcelIsFun), so check out his Power Query playlist, and get started, or improve your current skills. (Level-Int/Adv)

Also see: My Excel Products || Previous Issues

Not the Alps

Last Friday, an Alberta Clipper blew through here in the morning, and caused traffic havoc during rush hour. Fortunately, I work from home, so my commute wasn't affected. Mid-morning, I snapped the picture on the left, and it looks like I was working in the Alps! The photo on the right was taken mid-afternoon, and it looks like a whole different world (no filters or colour enhancements on the photos).

Have a happy holiday season, and I'll see you again on January 7th, 2020.

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser:
I'll also post any article updates or corrections there.

That's it for this week! If you have any comments or questions, send me an email.

Debra Dalgleish
[email protected]

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

contextures newsletter info


Last updated: December 10, 2019 11:29 AM