202R4 Functions With Criteria, for Spreadsheets

 |  No Comments

The greatest power of spreadsheets is that they can be programmed (the formula with functions) to do the maths as you update the data

We have already seen how to create basic formulae in this post, combining these with built in functions

Here are functions that only include numbers in our sum, count or average if they meet a specific criteria (search term)

The search criteria (search term) can be

  • a cell reference (ie the cell contents)
  • a constant value (eg “London”)
  • a wildcard (eg A*, anything starting with A)

The conditions of the search are referred to as critera1, criteria2, etc, checking:

  • If a number is greater than another number >
  • If a number is smaller than another number <
  • If a number is equal to something =
  • If text is equal to something “in quotes”

Searching for a Single Criteria

criteriaRange being where to find the criteria (eg the values being searched) – comes first

criteriaRange and the range being calculated can be the same

*[range of values] optional. If omitted, criteriaRange is used

SUMIFCOUNTIFAVERAGEIF
  • sum if matched
    =sumif(criteriaRange, search term, [range of values being added]*)
  • =SUMIF(criteriaRange, search term)
    =SUMIF(A2:A5,”London”)
    =SUMIF(A2:A5,A4)
  • count if matched (no range of values to calculate)
  • =COUNTIF(criteriaRange, search term)
    =COUNTIF(A2:A5,”London”)
    =COUNTIF(A2:A5,>A4)
  • average values that match a  condition
  • =AVERAGEIF(criteriaRange, search term (in range), [range of numbers to average]*)
    * optional
  • =AVERAGEIF(criteriaRange, search term)
    =AERAGEIF(A2:A5,”London”)
    =AVERAGEIF(A2:A5,>A4)

Searching for Multiple Criteria

criteriaRange being where to find the criteria (eg the values being searched) – comes last

searchRange being the data corresponding with the row values, the “searched range”
– this now comes first

SUMIFSCOUNTIFSAVERAGEIFS
  • sum if all criteria conditions match
  • =SUMIF(searchRange, criteriaRange1, criteria1, criteria2Range, criteria2, etc)
  • =SUMIFS(A2:A9, B2:B9, “=A*”, C2:C9, “Tom”)
    • adds the number of products that begin with A and were sold by Tom
      using the wildcard character * “=A*” (wildcard meaning anything starting with A) to search for matching product names in Criteria_range1 B2:B9,
      and
      matches the name “Tom” in Criteria_range2 C2:C9
      then adds the numbers in search_range A2:A9 that meet both conditions
  • include in the count if all criteria conditions match
  • =COUNTIF(search range, criteriaRange1, criteria1, criteria2 etc)
  • =COUNTIFS(A2:A9, B2:B9, “=A*”, C2:C9, “Tom”)
  • find average of the range using numbers which match all criteria conditions
  • =AVERAGEIF(search range, criteriaRange1, criteria1, criteria2 etc)
  • =AVERAGEIFS(A2:A9, B2:B9, “=A*”, C2:C9, “Tom”)
VIDEOABOUT

  • 13+ minutes
  • Why use Excel SUMIFS instead of SUMIF
    Excel SUMIF function
    Excel SUMIFS function
  • (6) SUMIFS with wildcards
  • SUMIFS with comparison operators
  • (9) Excel AVERAGIFS function
  • Excel COUNTIF function
  • Excel COUNTIFS function
  • (11.45) Excel Tables for dynamic referencing

SUMIF/S EXCOUNTIF/S EXAVERAGEIF/S EX
Quantity Sold Product Salesperson
5 Apples Tom
4 Apples Sarah
15 Artichokes Tom
3 Artichokes Sarah
22 Bananas Tom
12 Bananas Sarah
10 Carrots Tom
33 Carrots Sarah
  1. how much fruit was sold by Tom?
  2. how much fruit was sold by Sarah?
  3. how many carrots were sold?
Salesperson Exceeded Q1 quota Exceeded Q2 quota Exceeded Q3 quota
Davidoski Yes No No
Burke Yes Yes No
Sundaram Yes Yes Yes
Levitan No Yes Yes
  1. Count how many times Davidoski exceeded a sales quota for periods Q1, Q2, and Q3 (only in Q1)
  2. Count how many salespeople exceeded both their Q1 and Q2 quotas (Burke and Sundaram)
  3. Count how many times Levitan and Burke exceeded the same quota for periods Q1, Q2, and Q3 (only in Q2).
Property Value Commission Salesperson
100000 7000
200000 14000
300000 21000
400000 28000
  1. Average of all commissions less than 23000
  2. Average of all property values less than 250000
  3. Average of all property values less than 95000
  4. Average of all commissions with a property value greater than 250000

 

Share this by:

Share on SMS Share on WhatsApp Share on LinkedIn
Tags: , , ,