Contact Us : 800.874.5346 International: +1 352.375.0772
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.
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):
| A | B | C | D | |
| 1 | Salespeson | Sales | Bonus | Bonus |
| 2 | Aaron | 16,000 | Yes | |
| 3 | Betty | 13,000 | No | 0 |
| 4 | Carl | 12,000 | No | 0 |
| 5 | Daisy | 25,000 | Yes | 500 |
| 6 | Eric | 19,000 | Yes | 380 |
| 7 | Francis | 16,000 | Yes | 320 |
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.
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):
| A | B | C | D | |
| 1 | Salespeson | Sales | Bonus | Bonus |
| 2 | Aaron | 16,000 | Yes | |
| 3 | Betty | 13,000 | No | 0 |
| 4 | Carl | 12,000 | No | 0 |
| 5 | Daisy | 25,000 | Yes | 500 |
| 6 | Eric | 19,000 | Yes | 380 |
| 7 | Francis | 16,000 | Yes | 320 |
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).
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).
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):
| A | B | C | D | |
| 1 | Salespeson | Sales | Gender | Bonus |
| 2 | Aaron | 16,000 | M | 320 |
| 3 | Betty | 13,000 | F | 0 |
| 4 | Carl | 12,000 | M | 100 |
| 5 | Daisy | 25,000 | F | 500 |
| 6 | Eric | 19,000 | M | 380 |
| 7 | Francis | 16,000 | F | 320 |
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.
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):
| A | B | C | D | |
| 1 | Salespeson | Sales | Gender | Bonus |
| 2 | Aaron | 16,000 | M | 320 |
| 3 | Betty | 13,000 | F | 0 |
| 4 | Carl | 12,000 | M | 100 |
| 5 | Daisy | 25,000 | F | 500 |
| 6 | Eric | 19,000 | M | 380 |
| 7 | Francis | 16,000 | F | 320 |
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.
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):
| A | B | C | D | |
| 1 | Salespeson | Sales | Gender | Bonus |
| 2 | Aaron | 16,000 | M | 320 |
| 3 | Betty | 13,000 | F | 0 |
| 4 | Carl | 12,000 | M | 100 |
| 5 | Daisy | 25,000 | F | 500 |
| 6 | Eric | 19,000 | M | 380 |
| 7 | Francis | 16,000 | F | 320 |
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).
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):
| A | B | C | D | |
| 1 | Salespeson | Sales | Gender | Bonus |
| 2 | Aaron | 16,000 | M | 320 |
| 3 | Betty | 13,000 | F | 0 |
| 4 | Carl | 12,000 | M | 100 |
| 5 | Daisy | 25,000 | F | 500 |
| 6 | Eric | 19,000 | M | 380 |
| 7 | Francis | 16,000 | F | 320 |
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.
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!
Excel Lessons
Excel Basics
Excel Shortcuts
Excel Calculation Rules
Cell References
Excel Functions
Function Basics
Working with Numbers
Working with Datetime Data
Working with Text Data
Logical Testing
Summarizing a group of data
Filtering and Sorting
Lookup Functions
Tables
Pivot Tables
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
