Conditional aggregations

Data Analysis in Google Sheets

James Chapman

Curriculum Manager, DataCamp

Recap...

 

Conditional functions:

  • IF()

 

Aggregation functions:

  • COUNT()
  • SUM()
  • AVERAGE()
  • Many more!
Data Analysis in Google Sheets

COUNTIF()

Syntax:

COUNTIF(range, criterion)
  • criterion:
    • String to match, e.g., "United Kingdom"
    • Number to match, e.g., 150
    • String containing a number and comparison operator, e.g., ">9"
Data Analysis in Google Sheets

COUNTIF()

Syntax:

COUNTIF(range, criterion)

Counting how many games were pledged more than ten thousand dollars.

Data Analysis in Google Sheets

COUNTIF()

Syntax:

COUNTIF(range, criterion)

Counting how many games were pledged more than ten thousand dollars.

Data Analysis in Google Sheets

COUNTIF()

Syntax:

COUNTIF(range, criterion)

Counting how many games were pledged more than ten thousand dollars.

Data Analysis in Google Sheets

COUNTIF()

Syntax:

COUNTIF(range, criterion)

Counting how many games were pledged more than ten thousand dollars.

Data Analysis in Google Sheets

COUNTIFS()

COUNTIF() Syntax:

COUNTIF(range, criterion)

 

COUNTIFS() Syntax:

COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])
Data Analysis in Google Sheets

COUNTIFS()

Syntax:

COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])

Counting the number of kickstarter games with over ten thousand dollars pledged and with a goal over five thousand dollars.

Data Analysis in Google Sheets

COUNTIFS()

Syntax:

COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])

Counting the number of kickstarter games with over ten thousand dollars pledged and with a goal over five thousand dollars.

Data Analysis in Google Sheets

COUNTIFS()

Syntax:

COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])

Counting the number of kickstarter games with over ten thousand dollars pledged and with a goal over five thousand dollars.

Data Analysis in Google Sheets

COUNTIFS()

Syntax:

COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])

Counting the number of kickstarter games with over ten thousand dollars pledged and with a goal over five thousand dollars.

Data Analysis in Google Sheets

SUMIF()

Syntax:

SUMIF(range, criterion, [sum_range])
  • range: Range to apply the criterion to
  • sum_range (optional): Can specify a different range to sum
Data Analysis in Google Sheets

SUMIF()

Syntax:

SUMIF(range, criterion, [sum_range])

The sum of the pledged column for pledged amounts above ten thousand dollars.

Data Analysis in Google Sheets

SUMIF()

Syntax:

SUMIF(range, criterion, [sum_range])

The sum of the pledged column for pledged amounts above ten thousand dollars.

Data Analysis in Google Sheets

SUMIF()

Syntax:

SUMIF(range, criterion, [sum_range])

The sum of the goal column for pledged amounts above ten thousand dollars.

Data Analysis in Google Sheets

SUMIF()

Syntax:

SUMIF(range, criterion, [sum_range])

The sum of the goal column for pledged amounts above ten thousand dollars.

Data Analysis in Google Sheets

SUMIFS()

Syntax:

SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

Summing the pledged amounts of kickstarter games with over ten thousand dollars pledged and with a goal over five thousand dollars.

Data Analysis in Google Sheets

SUMIFS()

Syntax:

SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

Summing the pledged amounts of kickstarter games with over ten thousand dollars pledged and with a goal over five thousand dollars.

Data Analysis in Google Sheets

SUMIFS()

Syntax:

SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

Summing the pledged amounts of kickstarter games with over ten thousand dollars pledged and with a goal over five thousand dollars.

Data Analysis in Google Sheets

Conditional averages

 

Other conditional aggregation functions:

AVERAGEIF(criteria_range, criterion, [average_range])

AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
Data Analysis in Google Sheets

Let's practice!

Data Analysis in Google Sheets

Preparing Video For Download...