This post is the second part of a Formulas-You-Need-to-Know series. If you haven’t seen the first part, you’ll want to start here.
But assuming you’re up to speed, get ready! This is another game changer.
Formulas are an under-appreciated time saver in the spreadsheet game. You already saw how we can use them to automatically remove spaces in cells, apply proper formatting to names, and run quick calculations.
But sometimes, our data is more nuanced. We can’t just sum or average the numbers in a column to extract meaningful information.
That’s where conditional formulas come in.
First, if you’re new to formulas…
In case you missed this from the Part 1 post, here’s what you need to know:
1. All formulas begin with an equals sign (=). This is true whether you’re using Excel or Google Sheets.
2. Then you enter the formula and its reference. The “reference”, in parentheses, refers to the cell/s you want the formula to run against (ex. A2, B2:B6). If you’re still confused about this, you’ll see what I mean in the screenshots below.
3. Copy and paste the formula down a column to quickly make changes.
Spreadsheet Formulas, Part 2:
3 Simple Conditional Formulas
Conditional Formulas (totally doable ●●●○)
1) COUNTIF
counts the number of cells in a range that meet a certain condition
The COUNTIF function counts all cells in a given range that meet certain criteria.
=COUNTIF (a , b)
where….
a = the range of cells you want counted (ex. A2:A7)
b = the criteria each cell must meet
The formula below counts the number of gifts from B2 to B5, where the gift amount is greater than $25.
2) SUMIF
sums the values in a range if they meet a certain condition
The SUMIF function adds all values in a given range that meet a certain criteria.
Keep in mind that the structure is a bit different than it is with COUNTIF.
=SUMIF (a , b, c)
where….
a = the range of cells that should be evaluated for criteria (ex. A2:A7)
b = the criteria each cell must meet, from a
c = the range of corresponding cells that should be summed (ex. B2:B7)
In the example below, we want to sum all gifts that are greater or equal to $25. Note that the cells we’re checking for the criteria (part a) are the same cells we want to sum (part c). That’s why the references are the same in the formula.
But what if we want to sum values based on a different column? This formula sums all gifts that were made in the year 2020, by checking the criteria against column C.
3) AVERAGEIF
calculates the mean of values in a range, if they meet a certain condition
Guess what? AVERAGEIF pretty much works the same way as SUMIF. The only exception is that instead of choosing a sum range, you’ll choose an average range.
=AVERAGEIF (a , b, c)
where….
a = the range of cells that should be evaluated for criteria (ex. A2:A7)
b = the criteria of the cells, from a
c = the range of corresponding cells that should be averaged (ex. B2:B7)
Final tips when using these Formulas….
1. The criteria for these formulas doesn’t have to be numerical. If you’d like to check for text, you can do that as well – just put the exact text in between quotation marks.
2. Before pasting your formula, you may need to anchor your references. As you copy and paste a formula down a column, the references change with it. Anchors prevent this from happening. Here’s a quick video explaining how they work.
3. The formulas above can only accommodate a single condition. If you want to learn how to calculate based on multiple conditions, don’t worry. That’s up next in the series!
Stay tuned for the next post in this series, which will take our conditional formula skills up a notch.
Share your thoughts!