2. Conditional Arithmetic

 
Subtitles Enabled
Replay Lesson

Next lesson: Max, Min and Average Functions

Watch next lesson
100%

Overview

COUNTIF and SUMIF apply conditions to basic arithmetic functions. This enables us to calculate subtotals such as total revenue for a given month, location and product type.

Lesson Notes

Conditional equations

=COUNTIF: count the number of cells in a range that fit one criterion
=SUMIF: sum the values in a range that fit one criterion
=COUNTIFS: count the number of cells in a range that fit multiple criteria
=SUMIFS: sum the values in a range that fit multiple criteria
- Use inverted commas when writing criteria

Keyboard shortcuts

CTRL + SHIFT + →: Select all cells within data region
CTRL + →: Move to the last cell in the data region

Transcript

In the previous lesson we used SUM and COUNT to calculate the Total Revenue and total number of orders from January to March.

In this lesson, we're going to use two related functions, COUNTIF and SUMIF, which enable us to apply conditions to these calculations.

Let's say for example I want to find the number of orders that are greater than $30,000 in value.

I'll write “=countif” and open a bracket.

The first argument that Excel wants us to enter is the Range, which will be the Order Dollar Amount column.

Next I must enter my Criteria which will need to be in inverted commas, so I'll open inverted commas and write greater than 30,000.

I'll then close the inverted commas, close the bracket and type Enter.

And this tells me that 13 orders are greater than $30,000 in value.

To find the Total Revenue for these orders we use SUMIF.

So I'll write “=sumif” and open a bracket.

Again, the Range will be the Order Dollar Amount column, the Criteria will be greater than 30,000, and the Sum Range will also be the Order Dollar Amount column.

I'll then close the bracket and press Enter.

And this tells me that the 13 orders provide $584,000 in Total Revenue.

The SUMIF calculation first identifies the values in the Range that fulfill the Criteria.

It then sums the corresponding values together in the Sum Range.

In this example, the Range and the Sum Range are the same Order Dollar Amount column.

However, most of the time, the Range and the Sum Range will be different columns.

Let's see this with another example where I want to calculate the Total Revenue for orders made in March.

Again, I'll write “=sumif” and open a bracket.

The Range will be the Order Date, which I'll select with Ctrl + Shift + Up Arrow, and the criteria will be greater than or equal to the 1st of March 2012.

I'll then close the inverted commas and write a comma, and this time around the Sum Range will be the Order Dollar Amount column.

I'll then close the bracket and press Enter.

And this tells me that for March we made $244,000 in Revenue.

COUNTIF and SUMIF are great when you want to apply a single condition to a column.

However, if you want to apply multiple conditions, we use COUNTIFS and SUMIFS.

Say for example we want to count the number of Tablet orders in March.

I'll write “=countifs” and open a bracket.

The Criteria Range one will be the Product Type and the Criteria will be Tablet.

And the Criteria Range two will simply be the Order Date, and the Criteria will be greater than or equal to the 1st of March 2012.

I'll then close the bracket and press Enter.

And this tells me that we had three Tablet orders in March.

Although formulas using these functions can get quite long, they're actually easy to build because Excel tells you along the way which term is needed next.

All you need to do is understand what Range, Criteria and Sum Range mean to use these formulas correctly.