Sign in or start a free trial to avail of this feature.
2. Conditional Arithmetic
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.
Using COUNTIF (00:22)
COUNTIF lets us count the number of cells in a range that meet a specified condition, for example the number of orders above a certain value.
COUNTIF takes two arguments. First, the range is the range of cells which we want to count from. Second, the criteria is the condition that must be met for a cell to be counted. This must be in inverted commas, for example “>30000” is the criteria we would use to count the number of orders above $30,000.
Using SUMIF (01:02)
SUMIF lets us sum the cells in a range which meet a particular criteria, for example the revenue from all orders above a certain value.
SUMIF takes three arguments. First is the range. As with COUNTIF, this is the range of cells where we will check the criteria. Second is the criteria, which is the condition to be met for an entry to be included in the sum. Third is the sum range. This is the range of cells that we will add if the condition is met.
The range and sum range can be the same, but they’re usually different. For example, we can use SUMIF to find the total revenue for orders made in March. In this instance, the range will be the order dates and the sum range will be the order amounts.
Using COUNTIFS (02:49)
COUNTIFS is used to count the number of cells in a range that meets more than one condition, for example orders made in March that are for tablets.
The number of arguments depends on the number of conditions. For each condition, we define the range and the criteria, like with COUNTIF. The function then counts the number of rows that meet all of the conditions.
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 equals COUNTIF and open a bracket.
The first argument that Excel wants us to enter is the range, which would be the order dollar amount column.
Next, I'm center 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 closing 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 equals SUMIF and open a bracket. Again, the range would be the order dollar amount column.
The criteria would 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 equals SUMIF and open a bracket.
The range would be the order date, which I'll select with Control + Shift + Up Arrow, and the criteria will be greater than or equal to the 1st of March, 2012.
I'll then close inverted commas and write a comma, and this time round, the sum range will be the order dollar amount column.
I'll then close the bracket and press Enter, and this tells me that from 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 equals COUNTIFS and open a bracket.
The criteria range one, would be the product type, and the criteria will be tablet.
And the criteria range two, would 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.