Contact Us : 800.874.5346 | International: +1 352.375.0772
Contact Us : 800.874.5346 | International: +1 352.375.0772

Excel Functions: Summarizing a group of data

Data Analytics Lesson 10: Excel Functions and Summarizing Data

At times, data analytics works with a subset of the dataset (a cluster) rather than the whole dataset. Logical testing helps to group data sharing certain characteristics. Summary statistics summarize a set of data using a single value. This blog introduces some functions combining the two to summarize a group of data.

Single-Criterion Summary

  countif   function

The   countif   function counts the number of instances in a range meeting the criterion. It has the following syntax:

= countif(range, criteria)

Assume the following dataset (you may paste it to Excel):

ABCD
1SalespesonSalesBonusBonus
2Aaron16,000Yes
3Betty13,000No0
4Carl12,000No0
5Daisy25,000Yes500
6Eric19,000Yes380
7Francis16,000Yes320

Example 1:
The formula = countif(C2:C7, “=Yes”) counts the number of salespersons eligible for a sales bonus. It returns the number 4, corresponding to Aaron, Daisy, Eric, and Francis.

Example 2:
The formula = countif(D2:D7, “>300”) counts the number of salespersons receiving bonuses over $300. It returns the number 3, corresponding to Daisy, Eric, and Francis.

Example 3:
The formula = countif(D2:D7, “”) counts the number of salespersons whose information about bonuses is missing. It returns the number 1, corresponding to Aaron.

  sumif   function

The   sumif   function adds up the numerical values in the sum range that meet the criterion in the criterion range. It has the following syntax:

= sumif(range, criteria, [sum_range])

Assume the following dataset (you may paste it to Excel):

ABCD
1SalespesonSalesBonusBonus
2Aaron16,000Yes
3Betty13,000No0
4Carl12,000No0
5Daisy25,000Yes500
6Eric19,000Yes380
7Francis16,000Yes320

Example 1:
The formula = sumif(B2:B7, “>18000”, D2:D7) adds up the total bonus received by salespersons making sales over $18,000. It returns the value of 880, corresponding to the total bonuses received by Daisy ($500) and Eric ($380).

Example 2:
The formula = sumif(D2:D7, “”, B2:B7) adds up the total sales by salespersons whose information about sales bonuses is missing. It returns the value of $16,000, corresponding to the sales by Aaron ($16,000).

  averageif   function

The   averageif   function computes the mean of the values in an average range that meet the criterion in the criterion range. It has the following syntax:

= averageif(range, criteria, [sum_range])

Assume the following dataset (you may paste it to Excel):

Example 1:
The formula = averageif(B2:B7, “>18000”, D2:D7) adds up the average bonus received by salespersons making sales over $18,000. It returns the value of 440, corresponding to the average bonuses received by Daisy ($500) and Eric ($380).

Example 2:
The formula = averageif(C2:C7, “=Yes”, B2:B7) computes the average sales by salespersons who are eligible for sales bonuses. It returns the value of 19000, corresponding to the average sales by Aaron ($16,000), Daisy ($25,000), Eric ($19,000), and Francis ($16,000).

Multi-Criterion Summary

  countifs   function [Available in Excel 2019 or later versions]

The   countifs   function counts the number of instances in ranges meeting the criteria. It is equivalent to combining the   countif   function and the   and   function. It has the following syntax:

= countifs(criteria_range1, criteria1, [criteria_range2, criteria2], …)

Assume the following dataset (you may paste it to Excel):

ABCD
1SalespesonSalesGenderBonus
2Aaron16,000M320
3Betty13,000F0
4Carl12,000M100
5Daisy25,000F500
6Eric19,000M380
7Francis16,000F320

Bonus Policy: No sales bonuses are offered to salespersons with sales less than $15,000.

Example 1:
The formula =countifs(B2:B7, “>18000”, C2:C7, “=M”) counts the number of male salespersons making sales over $18000. It returns the number of 1, corresponding to Eric.

Example 2:
The formula =countifs(B2:B7, “<15000", D2:D7, "<>0″) counts the number of salespersons receiving bonuses not in accordance with the bonus policy. It returns the value of 1, corresponding to Carl.

  sumifs   function [Available in Excel 2019 or later versions]

The   sumifs   function adds up the values in the sum range that meet all the criteria in the criteria ranges. It is equivalent to combining the  sumif  function and the   and   function. It has the following syntax:

= sumifs(sum_range,criteria_range1, criteria1, [criteria_range2, criteria2], …)

Assume the following dataset (you may paste it to Excel):

ABCD
1SalespesonSalesGenderBonus
2Aaron16,000M320
3Betty13,000F0
4Carl12,000M100
5Daisy25,000F500
6Eric19,000M380
7Francis16,000F320

Bonus Policy: No sales bonuses are offered to salespersons with sales less than $15,000.

Example 1:
The formula = sumifs(D2:D7, B2:B7, “>18000”, C2:C7, “=F”) adds up the bonuses of female salespersons making sales over $18000. It returns the value of 500, corresponding to the bonus received by Daisy.

Example 2:
The formula = sumifs(D2:D7, B2:B7, “<15000", D2:D7, "<>0″) adds up the bonuses granted not in accordance with the bonus policy. It returns the value of 100, corresponding to the bonus received by Carl.

  averageifs   function [Available in Excel 2019 or later versions]

The   averageifs   function computes the mean of values in the average range that meet all the criteria in the criteria ranges. It is equivalent to combining the   averageif   function and the   and   function. It has the following syntax:

= averageifs(average_range,criteria_range1, criteria1, [criteria_range2, criteria2], …)

Assume the following dataset (you may paste it to Excel):

ABCD
1SalespesonSalesGenderBonus
2Aaron16,000M320
3Betty13,000F0
4Carl12,000M100
5Daisy25,000F500
6Eric19,000M380
7Francis16,000F320

Bonus Policy: No sales bonuses are offered to salespersons with sales less than $15,000.

Example:
The formula = averageifs(B2:B7, C2:C7, “=M”, D2:D7, “>300”) computes the average sales by male salespersons receiving bonuses over $300. It returns the value of 17500, corresponding to the average sales by Aaron ($16,000) and Eric ($19,000).

  maxifs   and   minifs   functions [Available in Excel 2019 or later versions]

The   maxifs   and   minifs   finds the maximum (minimum) value in the max (min) range that meet all the criteria in the criteria ranges. They have the following syntax:

= maxifs(max_range,criteria_range1, criteria1, [criteria_range2, criteria2], …)
=minifs(min_range,criteria_range1, criteria1, [criteria_range2, criteria2], …)

Assume the following dataset (you may paste it to Excel):

ABCD
1SalespesonSalesGenderBonus
2Aaron16,000M320
3Betty13,000F0
4Carl12,000M100
5Daisy25,000F500
6Eric19,000M380
7Francis16,000F320

Example 1:
The formula = maxifs(B2:B7, C2:C7, “=M”) finds the maximum sales by male salespersons. It returns the value of $19,000, corresponding to the sales by Eric.

Example:
The formula = minifs(B2:B7, C2:C7, “=F”, B2:B7, “<20000") finds the minimum bonus received by female salespersons having sales less than $20,000. It returns the value of 13000, corresponding to the sales by Betty.

Learning Excel Data Analytics with Gleim

At Gleim, we know learning data analytics is vital for future certified accountants. That is why we are offering this series of data analytics blogs and continually updating all of our review materials for CPA, CMA, and CIA with the necessary information you need to pass your exam the first time!

We’ll continue our weekly blog series. Check back regularly for all exam news and study tips!

Python Lessons
Python Basics
Conditional Statements
Loops
Functions and Modules
Numerical Python (NumPy)
Pandas Basics
Pandas Data Capture and Cleansing
Merging and Grouping
Manipulating Text and Datetime Data
Visualization
Web Scraping
Errors and Exceptions