Counting and Summing up

COUNTING FORMULA EXAMPLES

  • =COUNTIF(Region,”North”)
  • Counts the number of rows in which the value in Region is “North

  • =COUNTIF(AvgCost,3400
  • Counts the number of rows in which AvgCost equals 400

  • =COUNTIF(Sales,”>500”)
  • Counts the number of rows in which Sales greater than 500

  • =COUNTIF(Sales,”<>100”)
  • Counts the number of rows in which Sales <> 100

  • =COUNTIF(Area,”?????”)
  • Counts the number of rows in which Area contains five letters

  • =COUNTIF(Region,”*h*”)
  • Counts the number of rows in which Region contains the letter H (not case sensitive)

  • =COUNTIFS(Month,”Jan”,Sales,”>200”)
  • Counts the number of rows in which Month = “Jan” and Sales > 200

  • {=SUM((Month=”Jan”)*(Sales>200))}
  • An array formula that counts the number of rows in which Month = “Jan” and Sales > 200

  • =COUNTIFS(Month,”Jan”,Region,”North”)
  • Counts the number of rows in which Month = “Jan” and Region = “North”

  • {=SUM((Month=”Jan”)*(Region=”North”))}
  • An array formula that counts the number of rows in which Month = “Jan” and Region = “North”

  • =COUNTIFS(Month,”Jan”,Region,”North”)+COUNTIFS(Month,”Jan”,Region,”South”)
  • Counts the number of rows in which Month = “Jan” and Region = “North” or “South”

  • {=SUM((Month=”Jan”)*((Region=”North”)+(Region=”South”)))}
  • An array formula that counts the number of rows in which Month = “Jan” andRegion = “North” or “South”

  • =COUNTIFS(Sales,”>=300”,Sales,”<=400”)
  • Counts the number of rows in which Sales is between 300 and 400

  • {=SUM((Sales>=300)*(Sales<=400))}
  • An array formula that counts the number of rows in which Sales is between 300 and 400

SUMMING FORMULA EXAMPLES

  • =SUMIF(Sales,”>200”)
  • Sum of all Sales over 200

  • =SUMIF(Month,”Jan”,Sales)
  • Sum of Sales in which Month = “Jan”

  • =SUMIF(Month,”Jan”,Sales)+SUMIF(Month,”Feb”,Sales) or “Feb”
  • Sum of Sales in which Month =”Jan”

  • =SUMIFS(Sales,Month,”Jan”,Region,”North”)
  • Sum of Sales in which Month=”Jan” and Region=”North”

  • =SUMIFS(Sales,Month,”Jan”,Region,”North”)
  • Sum of Sales in which Month=”Jan” and Region=”North”

  • {=SUM((Month=”Jan”)*(Region=”North”)*Sales)}
  • An array formula that returns the sum of Sales in which Month=”Jan” andRegion=”North”

  • =SUMIFS(Sales,Month,”Jan”,Region,”<>North”)
  • Sum of Sales in which Month=”Jan” and Region <> “North”

  • {=SUM((Month=”Jan”)*(Region<>”North”)*Sales)}
  • An array formula that returns the sum of Sales in which Month=”Jan” and Region <> “North”

  • =SUMIFS(Sales,Month,”Jan”,Sales,”>=200”)
  • Sum of Sales in which Month=”Jan” and Sales>=200 (Excel 2007 only)

  • {=SUM((Month=”Jan”)*(Sales>=200)*(Sales))}
  • An array formula that returns the sum of Sales in which Month=”Jan” and Sales>=200

  • =SUMIFS(Sales,Sales,”>=300”,Sales,”<=400”)
  • Sum of Sales between 300 and 400 (Excel 2007 only)

  • {=SUM((Sales>=300)*(Sales<=400)*(Sales))}
  • An array formula that returns the sum of Sales between 300 and 400