Summing formula examples

Table 3-4 shows a number of formula examples that demonstrate a variety of summing techniques.

Table 3-4: SUMMING FORMULA EXAMPLES

+ Open table as spreadsheet

Table 3-4: SUMMING FORMULA EXAMPLES

+ Open table as spreadsheet

Formula

Description

=SUMIF(Sales,">200")

Sum of all Sales over 200

=SUMIF(Month,"Jan",Sales)

Sum of Sales in which Month = "Jan"

Formula

Description

=SUMIF(Month,"Jan",Sales)+ SUMIF(Month,"Feb",Sales)

Sum of Sales in which Month ="Jan" or "Feb"

=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" (Excel 2007 only)

{=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",Region,"<>North")

Sum of Sales in which Month="Jan" and Region <> "North" (Excel 2007 only)

{=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

0 0

Post a comment