Contextures

Contextures News

Tricky Excel COUNTIF Formula

May 17, 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.

Count Specific Codes

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.

Lookup With Two Criteria

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.

Your Feedback

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?

Dashboard Webinars

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).

Excel Articles

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)

More Excel Articles -- Visit my Excel website for many more tutorials and videos. And for a bit of fun, read my weekly collection of entertaining Excel tweets.

Canadian Colours

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:

www.contextures.com/newsletter/excelnews2016/20160524ctx.html

Recommended Excel Products

  • Easy Data Entry: Add a popup list, to your worbook, so you can quickly enter items in a cell. The Data Validation Multi Select Premium (DVMSP) kit helps you set things up. Then, a list appears when you click a cell, so only valid items can be entered. Reduce data entry errors and typos. You can customize the list, to allow single or multiple selection, in specific columns.
  • Excel Chart Tools: If you build complex charts, save time with Jon Peltier's Excel Charting add-in. Don't waste hours trying to format the chart yourself!

___________

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.

  • Full News Package: Weekly newsletter, plus occasional emails so you won't miss out on special Excel deals, free webinars, new products, and other Excel news.

Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.

 

 

 

Search Contextures Sites

Excel Data Entry Popup List

 

 

 

 

Last updated: June 14, 2017 11:49 PM