# 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