In the past, we taught you how to become an Excel Bully, and while we don’t really condone stealing other kids’ lunch money, we do believe it’s an important first step. So if you haven’t yet read the beginner’s guide to Excel if statements, click here!
Excel has supplied us with a few close cousins to the IF statement, I call them the other IFs, they might be lesser known but they pack a valuable punch (and being a Bully, knowing how to punch is a good thing). They offer first, significant flexibility, and second, automation.
Excel Tip of the Day: If you’re doing anything manually these days, you’re bordering on heresy, so if you ever find yourself saying I wish Excel did this or that… It does, you just don’t know it yet!
So we’ve learned Logic plays a huge role in how an IF statement works (remember from beginning If statements: logic means a declarative sentence that is either true or false) but Excel likes to change its language when we advance even a little. When working with the other IFs we need to remember that Excel only relies on true statements (now the picture above makes sense!).
These are the other IFs and they carry out instructions only if a condition is met.
- =SUMIF: Adds the cells specified by a given condition or criterion
- =SUMIFS: Adds the cells specified by a given set of conditions or criteria
- =COUNTIF: Counts the number of cells within a range that meet the given condition
- =COUNTIFS: Counts the cells specified by a given set of conditions or criteria
- =AVERAGEIF: Finds the average for the cells specified by a given condition or criterion
- =AVERAGEIFS: Finds the average for the cells specified by a given set of conditions or criteria
Here is a brief example:
Syntax: =SUMIF(range,criteria,[sum range]) or in a language I can understand…
=SUMIF(what to match?, based on this true statement, [sum range if different than ‘what to match?’])
Let’s say we had a list of tenants and their Net Rentable Square Footage (NRSF) is located in excel range (A1:C10). We want to find out how much NRSF is comprised by tenants larger than 1,000 SF. The SUMIF formula would look something like this:
These other IF statements are very helpful in reducing manual arithmetic, and as always, I highly suggest you download and practice on our other if statements spreadsheet. As always, there’s no real practical reason to struggle through the blog when the downloadable spreadsheet will mold you into an expert in under five minutes (you’re welcome for that advice now). And as always, it’s free, enjoy.