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 `SUM`

ming 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

## Comments