Still feeling nervous for your exam? Check out the Excel Cram Kit (Exam 2) for practice modules that emulate the difficulty that you'll face on your exam. Each module includes a thorough step-by-step explanation where I walk you through the entire problem solving process!

## COUNTIF, SUMIF, AVERAGEIF explained

It's the annual Ohio State vs. Michigan college football game. One of the biggest rivalries in the nation. Your family is originated in Ohio, so you all are massive OSU fans. You relatives, however, live in the state of Michigan, and are not afraid to boast about their fandom in front of you Buckeyes.

At your annual family tailgate, you compete in a hot dog eating competition, like any normal Midwestern family would.

Since you're the data analyst of the family, your parents ask you to use your Excel wizardry to compute the total number of competitors, the total number of hot dogs ate, and the average number of hot dogs ate. However, since this hot dog eating competition is between OSU and Michigan fans, we need to compute these values separately.

To do so, we can use the COUNTIF, SUMIF, and AVERAGEIF functions! These functions all work very similar to their base functions (COUNT, SUM, and AVERAGE) with one key difference:

They **filter** for cells that meet a certain **condition**, then COUNT, SUM, or AVERAGE those rows.

In our case, this condition will be the team that the participant is representing.

## Coding a COUNTIF, SUMIF, AVERAGEIF

Below are the results of the hot dog eating competition:

We'll utilize the COUNTIF, SUMIF, and AVERAGEIF functions in orange cells on the right to compute the results of the competition.

### COUNTIF

To start, let's check out the template for COUNTIF functions:

=COUNTIF(**range**, **criteria**)

Let's begin by counting the number of participants belonging to OSU's fandom.

To begin, what is the **range** of values that we'll be counting? Those will be the values in column B that represent which school the participant is supporting.

=COUNTIF(B2:B11, **criteria**)

Next, our **criteria** is that the cells in B2:B11 equal "OSU", or else we don't want to count them. So, we'll place "OSU" into **criteria**.

=COUNTIF(B2:B11, "OSU")

Notice how our result is 6...

...and we have 6 OSU values in the table.

To copy this over to the Michigan value in G2, we'll write the same formula except replace "OSU" with "Michigan".

=COUNTIF(B2:B11, "Michigan")

This shows us we had a total of 4 Michigan values in the table.

### SUMIF

Here is the template for SUMIF functions:

=SUMIF(**range**, **criteria**, [**sum_range**])

With this, how can we compute the total number of hot dogs ate by OSU participants?

Our **range** is going to be the same as last time:

=SUMIF(B2:B11, **criteria**, **[sum_range]**)

Our criteria is additionally going to be the same:

=SUMIF(B2:B11, "OSU", **[sum_range]**)

Now, how can we determine the **[sum_range]**?

To determine your **[sum_range]**, ask yourself where the **values** are that you're wanting to **sum** are.

The values that we're wanting to sum exist in C2:C11, so we'll plug that into our **[sum_range]** like so!

=SUMIF(B2:B11, "OSU", C2:C11)

When we plug this into cell F3, we're able to determine that OSU participants ate 96 hot dogs.

To copy this over to Michigan's cell, just write the same formula in G2 but replace "OSU" with "Michigan".

=SUMIF(B2:B11, "Michigan", C2:C11)

This shows us that Michigan participants at a total of 62 hot dogs.

### AVERAGEIF

Here is the template for AVERAGEIF functions:

=AVERAGEIF(**range**, **criteria**, [**average_range**])

In terms of **range **and **criteria** for OSU's average number of hot dogs ate per participant in cell F4, once again they'll be the same as COUNTIF and SUMIF:

=AVERAGEIF(B2:B11, "OSU", **[average_range]**)

To compute **[average_range]**...

To determine your **[average_range]**, ask yourself where the **values** are that you're wanting to **average** are.

In our case, that's once again the values in cells C2:C11!

=AVERAGEIF(B2:B11, "OSU", C2:C11)

When we plug this into cell F4, we learn that OSU participants ate an average of 16 hot dogs.

To copy this over to G4 for Michigan participants, we just need to replace "OSU" with "Michigan".

=AVERAGEIF(B2:B11, "Michigan", C2:C11)

This shows us that Michigan participants ate on average 15.5 hot dogs.

## Practice problem

Given the following table detailing wins and losses for your favorite college football team, determine the total number of games won, the total number of points scored, and the average number of points scored in only games which your team won.

## Excel (Exam 2) Follow-along Guide

It's no secret you retain info better when you *write* it down. That's why I created the **Excel (Exam 2) Follow-along Guide** for you!

As you come upon key concepts highlighted in yellow, like this sentence here, you can **fill-in-the-blanks **on your Follow-along Guide so that you remember all the important stuff for later!

You can obtain the Excel (Exam 2) Follow-along Guide for **FREE** by entering your email below!

Next, let's dive into the SUMPRODUCT function!

### Follow-along Guide

*Fill-in-the-blanks for highlighted, key points in the concepts below!*

### Free Resources

*Each exam concept broken down in simple, real-world examples!*

Tools | Excel (Exam 2) Follow-along Guide | |

Lesson | AND vs. OR | |

Lesson | HLOOKUP | |

Lesson | IFERROR | |

Lesson | Nested IF vs. IFS | |

Lesson | Excel tables | |

Lesson | PivotTables | |

Lesson | COUNTIF, SUMIF, AVERAGEIF | |

Lesson | SUMPRODUCT | |

Lesson | 3D Cell References |

### Practice Problems

*Step-by-step walkthrough for each of the questions you need to be ready for!*

"I got this review for my 2nd CSE exam after not doing so well on the first one, and it was so helpful! Will definitely be using again in the future."

**Erin Graham***Oct. 26, 2021*

I’m a Miami University (OH) 2021 alumni who majored in Information Systems. At Miami, I tutored students in Python, SQL, JavaScript, and HTML for 2+ years. I’m a huge fantasy football fan, Marvel nerd, and love hanging out with my friends here in Chicago where I currently reside.