Conditional summary statistics
In Data Analysis in Google Sheeets, you saw how to use COUNTIF() to calculate summary statistics. Here's you'll take it one step further using the related COUNTIFS() function, which lets you pass multiple conditions to it.
Arguments to COUNTIFS() come in pairs: a range of values to filter on, best given as absolute addresses, and a condition. The condition is text consisting of
- a value to match, or
- a comparison operator (
=,<,>=, etc.) and a number.
For example, COUNTIFS(A2:A100, "DataCamp", B2:B100, ">10") counts the number of values where column A matches "DataCamp" and column B is greater than 10.
This exercise is part of the course
Intermediate Google Sheets
Exercise instructions
- In cell
H2, get the unique Area values usingUNIQUE(). - In column
I, useCOUNTIF()to get the count of each Area. Pass the data range fromA2toA45as absolute addresses, and the filter criteria fromH2toH9. - In column
J, useCOUNTIFS()to get the count of each area with more than20Swallow-tails. the first two arguments are the same as the previous step, then add a condition for columnDto be greater than20.
Hands-on interactive exercise
Turn theory into action with one of our interactive exercises
Start Exercise