Conditional aggregations

1. Conditional aggregations

Great work using those conditional and logical functions!

2. Recap...

In the last lesson, we saw the power of the conditional function, IF, to return values depending on a set of criteria. In Chapter 1, recall that we used summary statistics to explore our data. These functions are also referred to as aggregation functions, which means they perform a calculation over a group to summarize it. In this lesson, we'll combine these two concepts to discuss conditional aggregations, which allow us to calculate summary statistics based on conditions in our analysis.

3. COUNTIF()

The first conditional aggregation function we'll look at is COUNTIF, which counts cells in a specified range that meet some criterion. The criterion argument can either be a string to match, a number to match, or a string containing a number preceded by a comparison operator, such as greater-than nine, that will be evaluated.

4. COUNTIF()

Let's count how many kickstarter games were pledged more than 10,000 dollars using COUNTIF.

5. COUNTIF()

The first argument is the range of cells to count, which are the pledged values. Recall that using the cell range A2-colon-A includes every cell from A2 downwards. This means we don't have to scroll to find the last row index, and the count will automatically update if additional rows are added.

6. COUNTIF()

The second argument is the criterion, which we want to specify as being greater than 10,000.

7. COUNTIF()

We found that 57 games were pledged more than 10,000 dollars.

8. COUNTIFS()

COUNTIF is useful if we only have a single criterion, but to specify multiple criteria, we need to use COUNTIFS. The COUNTIFS syntax looks complicated at first glance, but if we look closely, it has the same flow as COUNTIF of specifying a range then a criterion. The only difference is that COUNTIFS allows us to specify multiple criteria over different ranges, and if all the criteria are met, the row is counted.

9. COUNTIFS()

Let's extend our COUNTIF example to count how many kickstarter games had more than 10,000 dollars pledged and also had a goal less than 5000 dollars.

10. COUNTIFS()

We can recycle the same range and criterion used before on the pledged column,

11. COUNTIFS()

and create a new criterion that the goal must be less than 5000, selecting the range from B2 downwards.

12. COUNTIFS()

This returns 11 games that vastly outperformed their modest goals.

13. SUMIF()

SUMIF functions the same way as COUNTIF, but aggregates using the sum of the cells rather than the count. There is an extra optional argument called sum_range. The range argument is what is checked by the criterion, and by default what is also summed, but we can specify a different range to sum using sum_range.

14. SUMIF()

We can use this formula to sum all of the pledged amounts above 10,000 dollars,

15. SUMIF()

which comes to nearly thirteen million.

16. SUMIF()

If we want to sum the goal column for games where more than 10,000 dollars was pledged, we can extend our function by passing the Goal range, B2-colon-B, to the sum_range argument,

17. SUMIF()

which returns two million. This short analysis shows that the goal for many of these successful kickstarter games was much smaller than what they actually achieved.

18. SUMIFS()

SUMIFS is very similar, but like COUNTIFS, it allows us to aggregate based on multiple criteria. We can use SUMIFS to find the sum of pledged amounts above 10,000 dollars that also had goals below 5,000 dollars.

19. SUMIFS()

The sum_range argument here has to be specified first, so the function knows which range to sum, in this case, the pledged amounts. Then, we specify the range to check against the criteria, and the criteria itself, as a series of pairs, just like COUNTIFS.

20. SUMIFS()

The formula returns just under 240,000 dollars.

21. Conditional averages

The AVERAGEIF and AVERAGEIFS functions have exactly the same syntax as SUMIF and SUMIFS, but aggregate using the average rather than the sum.

22. Let's practice!

Go forth and draw insights using conditional aggregations!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.