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 |

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.

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 |

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.

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 |

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

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