Tricky Excel COUNTIF Formula
May 24, 2016
Count specific codes in a list, and more, in this week's Excel news. If there are topics that you'd like to see covered in future newsletters, please let me know.
- Debra - ddalgleish @ contextures.com
Visit my Excel website for many more tutorials and videos.
It's easy to count things with the COUNTIF or COUNTIFS functions, and there are many examples on my website. Someone asked me a challenging question this week though -- how can you count cells that contain a specific code, when there might be similar codes that you don't want to count.
In the screen shot below, you can see what I mean. The formula should only count cells that contain "JK", but not in the string "JKA". On its own the COUNTIF function finds 7 cells with "JK", so I had to use 4 variations of COUNTIF, then get a total with SUM. Can you think of a shorter formula to do the same thing?
You can download the sample file, to see my SUM/COUNTIF formula, on the Excel Samples page of my website. It's in the Functions section, as FN0047 - Count Specific Codes in a List.
You can use VLOOKUP or INDEX/MATCH to do a lookup based on a single value, such as finding the price, based on a product code. But what if you have two pieces of information, such as a product name, and a size, and you want to find the price based on that information?
I've made a video that shows how you can do this with INDEX and MATCH, and you can watch the video and get the sample file on my website.
Last week I shared the tip that when you're entering a formula, you can press Ctrl+Shift+A to put all the arguments into the cell. Someone asked how to do that in Hungarian, so I checked with Kris Szabo, an Excel expert from Hungary. She did a bit of testing, and found that Ctrl+Shift+N works. In Kris' screen shot below, she shows how it works with the MATCH function. Have you run into other English shortcuts that don't work on your computer?
If you missed Mynda Treacy's free Excel Dashboard webinars last week, you can watch the replays for a few more days. You won't have the live interaction with Mynda, but you'll get some great tips and new skills during the 1 hour replay.
Also, if you're interested in Mynda's highly-rated Excel dashboard course, don't miss the deadline -- registration closes this Thursday May 26, at 8 PM (Pacific time zone).
Here are a couple of Excel articles I read recently, that you might find useful.
Data Viz -- On the VizWiz blog, Andy Kriebel posted a list of 12 books every great data analyst should read. (Level - Intermediate/Advanced)
Excel Tricks -- On Chandoo's blog, Hui has posted one final collection of Excel tips, from the blog's readers. I wasn't sold on the benefits of tip #8, but then I read tip #19, and decided to move my Clear Filters icon to the first position in the QAT. That will save me some time. (Level - Basic/Intermediate)
We went to the garden centre last week, to get our plants, and avoid the weekend crowds. It's overwhelming to see all the varieties and colours, so we usually stick to the plants that we've bought before, and avoid the exotic ones. So, we loaded up with tomato plants and marigolds, because even I have good luck with those!
For the planter by the front door, we got this red and white petunia, because we liked its Canadian colours. I hope it's still alive on Canada Day (July 1st)!
NOTE: If you have any problems with the links in the email, you can see this newsletter on my website -- copy this link and paste it into your browser:
Choose Your Excel News: Get the full Contextures news package, or the basic one. To change, click a link below, and use the sign-up form on that page.
Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.
Last updated: December 25, 2019 2:09 PM