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

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! |

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."