Administration Dashboard Application
Excel COUNTIF Issues
December 10, 2019
Block duplicate entries, COUNTIF difficulties, and additional, in this week’s Excel information. Visit my Excel web page for additional suggestions, tutorials and films, and look at the index for past problems of this e-newsletter.
Happy Vacations! This is the last newsletter for 2019, and I’ll be back again on January 7th.
Note: For some merchandise described beneath, I get paid a fee on revenue. That allows guidance the cost-free tutorials on my internet site.
Block Duplicates
In some workbooks, you could possibly require to block replicate entries in a column. For instance, we really don’t want 2 employees to have the similar ID range. See how to established up a personalized rule for that, with information validation. And retain studying, to see why COUNTIF can cause challenges for you.
Initially, here’s how to block copy entries:
- Pick out the cells exactly where you want the rule applied — A2:A3 in the monitor shot down below. (That array is named EmpIDs)
- On the Information tab, click on Info Validation, and for Let, find Personalized
- In the Formula box, style this components that checks for matching entries.:
- COUNTIF counts any matches in the EmpIDs range, for the quantity in mobile A2. If there is certainly more than one particular, the entry will be blocked
COUNTIF Difficulties: If your numbers are text, rather of actual quantities, COUNTIF will cause troubles. In the table down below, you can see that COUNTIF counts “0123” and “123” as (false) duplicates. Which is mainly because it treats text numbers like actual figures, so “0123” =123 and “123” =123.
Rather, use SUMPRODUCT. It treats text quantities as text, and “0123” is various from “123”
This is the knowledge validation formula to use, if you have “textual content” 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.
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
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.
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.
Debra Dalgleish
dsd@ contextures.com
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.
No comments:
Post a Comment