Excel COUNTIF Problems
December 10, 2019
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.
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:
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"
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.
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.
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
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)
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.
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20191210ctx.html
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.
Last updated: December 10, 2019 11:29 AM