by James Spellos, CMP, MOS

As you continue to learn more about Excel, the area where quantum leaps of productivity take place is in using functions. Everyone (at least everyone reading this) is well aware of the most basic function, @SUM. At the core of what Excel does, this function allows you to perform math on cells. It’s such a fundamental tool, that Excel doesn’t require you to even enter the function name (go ahead, type in =A1+B1+C1… it adds the cells without the @SUM reference).

Beyond @SUM is a universe of way cool tools that can, well, that can darn well do almost anything you need (short of making dinner, though Microsoft may be working on that). One that is often overlooked but incredibly useful is @COUNT. Did you know that the @COUNT function has five different variants allowing you to count (not sum… big difference: @SUM adds numbers; @COUNT totals the number of records or cells in your list) most efficiently.

No way, you say. Well, here they are, with a quick review of what they can do for you.

• @count – Tally the total number of records in your list
• @countblank – Total the number of blank cells in a column
• @counta – Total the number of non blank cells in a column
• @countif – Tally the number of records that meet a certain condition
• @countifs – Tally the number of records that meet multiple conditions

Those last two are extra special. Actually, @IF is an amazing function on its own, as is its near identical twin, @IFS. Having the ability to search for and only total fields that meet a condition is a powerful database tool.

Let’s say you have a list of all of your organization’s customers. You’ve done your work and kept the list of transactions up to date. @COUNTIF can let you find out lots of useful information. Need to know how many people have bought a product in the past three months? No problem. How about all of the customers in a particular state or city? Easy.  Only want to know how many bought your top selling widget? Piece of cake.

You may be thinking, “Well, if I can use IF with @COUNT, can I also use it with @SUM?” If you were thinking that, give yourself a gold star, because that is exactly correct. Both @SUMIF and @SUMIFS are powerful functions to add to your Excel tool belt (you do have one of those, don’t you?).

But what if you only wanted to count the customers from Ohio who bought your coolest widget… and do so in a filtered list? Can @COUNT really do that? Well, maybe not… but there is a function (let’s call it @COUNT’s really smart cousin) that can easily do that for you. And that function is…

Bummer. I think that’s all the space I have for now. But join me at TEC17 at the Excel Function Blow Out Sale session to find out that one, and 25 more. I can @COUNT the days till March.

For in-person training on this and many other topics, register for IAAP TEC17 today!