COUNTIF, SUMIF, AVERAGEIF | CSE 148 – Excel Exam 2

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)

With SUMIF, we're calculating the sum of the values in C2:C11, based on whether or not each number's corresponding school is OSU or Michigan.

With AVERAGEIF, we're calculating the average the values in C2:C11, based on whether or not each number's corresponding school is OSU or Michigan.

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.

In cell H2 to calculate the number of wins...

=COUNTIF(C2:C9, "Win")

In cell H3 to calculate number of points scored in wins...

=SUMIF(C2:C9, "Win", D2:D9)

In cell H4 to calculate the average number of points scored by opponents in wins...

=AVERAGEIF(C2:C9, "Loss", E2:E9)

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!

ToolsExcel (Exam 2) Follow-along Guide
LessonAND vs. OR
LessonHLOOKUP
LessonIFERROR
LessonNested IF vs. IFS
LessonExcel tables
LessonPivotTables
LessonCOUNTIF, SUMIF, AVERAGEIF
LessonSUMPRODUCT
Lesson3D 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

Rating: 5 out of 5.

Leave a Comment