Technology Solutions for Everyday Folks
Cartoon about functional programming

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

Comments

Post Comments

Restricted HTML

  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.