About this lesson
Let's look at how to use SUMFIS and COUNTIFS to calculate based on more than one criterion column and add numbers per month.
Download this lesson’s exercise file.Conditional Math 2.xlsx
Conditional Math Functions
Learn the basic uses of SUMIFS and COUNTIFS
When to use
These functions are useful to add or count based on multiple criteria.
- SUMIFS() syntax: =SUMIFS(Sum range, criteria range1, criteria1, criteria range2, criteria2, …..3, …3, etc)
- COUNTIFS() syntax: =COUNTIFS(criteria range1, criteria1, criteria range2, criteria2, ....3, ....3, etc.)
- The criteria range1 does not have to be the same as criteria range2, or 3, but it can be
- To see if a value in a criteria range falls between two values, you will use the same criteria range with 2 different criteria (e.g. >1, <5)
- Example 1: Total sales by Item and Location: SUMIFS with 2 different criteria and criteria ranges (and logic tests referring to text)
- Example 2: Count the number of transactions based on items and criteria: COUNTIFS using two different criteria and criteria ranges (with text as logic test)
- Example 3: Total sales per month: Uses SUMIFS on the same criteria range with 2 different criteria (between two numbers)
Lesson notes are only available for subscribers.