As If!

"And then he asked if I wanted to see his spreadsheet..."

In previous posts, we've looked at IF statements. We've used COUNTIF and IFERROR. But there are even more variations of IF hidden within Excel. (Did you know that you can even "What If?" with it?)

The IF clan helps you choose and play through different kinds of scenarios. So, let's pull up our workbook for basic statistics again (review it here; download it here) and give our IFs a workout. You don't have to be clueless any longer.

Since you're already familiar with COUNTIF, let's start with its brother, COUNTIFS: a way to count something based on multiple criteria. If you recall, we have a spreadsheet that has a list of students from two schools, along with their scores and achievement levels for tests in reading, writing, and 'rithematic.

Suppose you need to know how many students at School A scored in Level 2 of the Reading and Writing tests so you can set up some tutoring. If it was just one condition (e.g., how many students scored in Level 2 of Reading), COUNTIF would work just fine. But to get a number of students that satisfy both cases, we need to call in reinforcements. Notice that we need three columns of data: A (name of school), D (reading level), and F, (writing level). We also need three different identifiers: "A" for the school, and 2 for the reading and writing levels. We have data in rows 2 - 517 to count. To use COUNTIFS, find an empty cell and add =COUNTIFS(A2:A517,"A",D2:D517,2,F2:F517,2). Our answer? 32.

We can also make use of AVERAGEIF and AVERAGEIFS. If I just need to know the average score of the students meeting the math standards (levels 3 and 4), I can ask Excel to AVERAGEIF(G2:G517,">400"). But, if I want to find out the average score at School B on the math test, I need to AVERAGEIFS(G2:G517,A2:A517,"B"). We can add more conditions, too.

Why would you do that? As if!

And finally, there is SUMIF and SUMIFS. While there is not a good application of these formulas with the current data set, let's pretend for a moment. Whatever. Suppose we want to compare the total scores for reading, writing, and math for each school. To find the total number of points for Reading at the A school, for example, we can use SUMIF: =SUMIF(A2:A517,"A",C2:C517). But if I want to only find the total score points for students in Reading at School A if they scored at least 17 points on the Writing test, I can add conditions using SUMIFS:  =SUMIFS(C2:C517,A2:A517,"A",E2:E517,">17").

So, add these IFs to your Excel rotation. If you're having trouble keeping them straight, just remember the "IFS" versions---they will still work with only one condition, just like the versions that end in "IF."