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
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:
Similarly, I can use
SUMIF to tell me how much was spent on this particular item:
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"):
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)
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