Cell Counting Techniques!

It is frequently handy to be able to count certain types of cells in a worksheet range. The following are four common Excel Functions that accomplish this quite efficiently:

1. COUNT: Simply counts the number of cells in a range that contain a number.
2. COUNTA: Counts the number of non-blank cells in a range (including those with text).
3. COUNTBLANK: Counts the number of empty cells.
4. COUNTIF: Flexible counting function (that we will explore with some examples)

So how can I use the COUNTIF function, you might be asking. For a bit of Inspiration, the following are some examples (in each case, the formula uses a range named Data):

Count the number of cells that contain the word "Charles" (not case sensitive):
=COUNTIF(Data,"Charles")

Count the number of cells that contain the words "Charles" and “Mary”:
=COUNTIF(Data,"Charles") + COUNTIF(Data,"Mary")

Count the number of cells containing any text (ignoring the numbers):
=COUNTIF(Data,"*")

Count the number of 4-letter words:
=COUNTIF(Data,"????")

Count the number of cells containing text that begins with the letter "T":
=COUNTIF(Data, “T*”)

Count the number of cells that contain a value greater than or equal to 15:
=COUNTIF(Data,">=15")

Count the number of cells that contain a value from 5 to 20:
=COUNTIF(Data,">=5")-COUNTIF(data,">20")

Being able to count cells on your terms is a great tool to have in your Excel Tool Kit. Especially the versatile COUNTIF! (I could say, “Make your spreadsheets Count”, but that would be way too corny…).

Cheers!

You are the Excel Guru!

Magic
Using the keyboard to quickly effect major changes in an Excel workbook can leave the impression of Magic to the onlookers (including your boss) of a Skilled Excel Practitioner. Here are a couple new favorites of mine that can help you increase your status as Excel Guru (or Excel Master or Excel Boss if you prefer).

Let’s say that you have 12 columns of data for which you want to calculate the Sums. Simple matter, of course, but if you want to do it in True Guru Style, simply select the entire block of data, and press Alt and = on your keyboard. Presto! The sums for all of your columns will appear like Magic in the row below.

More Magic
You can do more Magic by once again selecting the entire range of data and choosing Average, Count, Max, or Min from the AutoSum icon on your toolbar. The calculations will Instantly Appear for all columns of data!

As with nearly all things Excel, there are several different ways of achieving the same results. The Key is to do it with Style and Panache! After all, that is only fitting for a true Excel Guru…

I am Here for You

Have you ever been working on an Excel project and gotten Stuck on making it work like you want it to? Have you ever thought to yourself, “There must be an easier way?” Have you ever wished you had the services of an Excel Expert (or another expert if you are one yourself)?

We have all been there, and if you have been doing Excel for a long time, you probably been there more than once.

I was looking over past posts and questions from readers and, (feeling curiously generous with my time right now), I have decided to make a Special Offer to All of My Kindle Blog Subscribers: Now through the end of June, if you have an Excel project or problem on which you could use some help, please email me at the following address. I am devoting up to ½ Hour to each of my subscribers for any Excel help they need through June.

rdelamartre@earthlink.net

This is NOT, I assure you, some sleazy way to get you to buy anything, (services or whatever), but merely an altruistic offer to my Kindle Readers for whom I am truly grateful.

I am here for you, so if you think I might be able to help, please send me an email. I will be delighted to hear from you!

All the best,

~Bob

Good Databases (Part 2)

Last week, we looked at what constitutes best practices in creating a Good Database. Now we are going to examine how to best Extract Information from your data.


First order of business is to Name Your Ranges. An easy trick to use is to select your entire database (including the names of the ranges in the header) and go to Define Names and choose Create Names from Selection. Put a check next to Top Row and click OK – Voila, you have just named all of your ranges!

Now is when the fun begins! (This may sound a bit complex at first, but the concept is actually very simple). What we will be working with is called the SUMPRODUCT function.

SUMPRODUCT is an amazingly versatile function which can be used in many different ways to extract information from databases. You use its simple logic of “Yes or No” (0 or 1) to multiply corresponding components in user defined arrays, and return the sum of those products. The format is expressed as =SUMPRODUCT((array1)*(array2)* ...).

So enough talk! Let’s actually use it and see what it can do. Duplicate the database below, (or use one of your own), and name the ranges. Then, (using the example database), in any cell outside the database (if you wish, it can be on a separate worksheet in the same workbook) enter =SUMPRODUCT((Office = “West”)*(Sales)). Please note the use of Double Parentheses and Quotation Marks around West.

So, what is the SUMPRODUCT formula doing in this case? It is going through your entire database (it would work just as well with a database of 10,000+ records) and each time the first parameter of (Office = “West”) is true it assigns the number 1 and each time it is false it assigns the number 0. Then it multiplies times the Sales figure and Adds them all up!

Please hear me when I say this: If you are into Excel, please give this a try. I can guarantee you that you will be a Total Rock Star in your company!

Next week: One last, really cool example (a bit more complex) of using SUMPRODUCT with a database. See you then!