Simple, Yet Powerful Excel/Sheets Functions

As a reflection exercise for a project I wrapped up a bit ago, combined as an exercise to make Future Me's life a Better Place, I pulled together a simple Google Sheets workbook to make a quick 'calculator' and balancing mechanism to help keep track of a couple datasets. And in so doing, I again befriended a couple of super simple, and super powerful functions:

SUMIF and COUNTIF

These (and a couple of their variations) are the grown-up version of the very elementary SUM and COUNT functions, where they get together with the logical IF and enjoy a platonic relationship. By combining with IF, these functions are immediately more useful to make a simple calculation when the parent dataset is more complex than a list of like things.

Take for example my hypothetical situation. A sheet/workbook with a list of items purchased and sold and unit prices. I just need a quick calculator to tell me the totals of what was purchased, what is currently available, and what has been sold. Since all the input (purchases) are recorded one-by-one as individual lines in the same sheet, I can use COUNTIF to tell me how many of a given item was originally purchased:

=COUNTIF('Purchased'!A:A,"Item Name")

Similarly, I can use SUMIF to tell me how much was spent on this particular item:

=SUMIF('Purchased'!A:A,"Item Name",'Purchased'!B:B)

In SUMIF, the result is SUMming across column B where the pattern matches column A. Pretty rad!

So my calculator can tell me the total number of items purchased and their total cost in two pretty simple functions. I can use the same functions to tell me what's been sold and the resulting total by using the same logic (though in this case sourced from a sheet called "Sold"):

=COUNTIF('Sold'!A:A,"Item Name")

=SUMIF('Sold'!A:A,"Item Name",Sold'!B:B)

Using simple math, it can be determined if things are out of balance (or give a pseudo-inventory of what's on hand):

=COUNTIF('Purchased'!A:A,"Item Name")-COUNTIF('Sold'!A:A,"Item Name")

=SUMIF('Purchased'!A:A,"Item Name",'Purchased'!B:B)-SUMIF('Sold'!A:A,"Item Name",'Sold'!B:B)

Obvious Caveats

First and foremost, this is decidedly not an inventory or sales management system. Merely a quick calculator based on some simplistic input/output data.

Additionally, this particular example doesn't scale well. But it's not intended to. The point is to right-size the solution to the problem at hand, not to beat a product into submission. In this example within a few minutes a quick-view calculation can be made based on the data readily available, without needing to create a shadow copy of what was available or worse, calculate by hand.

There are several useful variations of how these sort of functions can be leveraged. Give it a try!

Headline image via Medium