Monday, 27 January 2025

sumif and sumifs 20 examples

 The SUMIF function in Excel is used to sum the values in a range that meet a specified condition. Below are 20 examples of how to use SUMIF with sample data. Each example includes a description of the scenario, the formula, and the expected result.


Example Data

Assume the following data is in an Excel sheet:

AB
ProductSales
Apple100
Banana200
Apple150
Orange300
Banana250
Apple50
Orange400
Banana100
Apple200
Orange350

Examples

  1. Sum sales for "Apple"

    • Formula: =SUMIF(A2:A11, "Apple", B2:B11)

    • Result: 500 (100 + 150 + 50 + 200)

  2. Sum sales for "Banana"

    • Formula: =SUMIF(A2:A11, "Banana", B2:B11)

    • Result: 550 (200 + 250 + 100)

  3. Sum sales for "Orange"

    • Formula: =SUMIF(A2:A11, "Orange", B2:B11)

    • Result: 1050 (300 + 400 + 350)

  4. Sum sales greater than 200

    • Formula: =SUMIF(B2:B11, ">200")

    • Result: 1650 (250 + 300 + 400 + 350 + 200 + 150)

  5. Sum sales less than or equal to 100

    • Formula: =SUMIF(B2:B11, "<=100")

    • Result: 250 (100 + 50 + 100)

  6. Sum sales not equal to 200

    • Formula: =SUMIF(B2:B11, "<>200")

    • Result: 1800 (Total sales - 200)

  7. Sum sales for products starting with "A"

    • Formula: =SUMIF(A2:A11, "A*", B2:B11)

    • Result: 500 (Apple sales)

  8. Sum sales for products ending with "e"

    • Formula: =SUMIF(A2:A11, "*e", B2:B11)

    • Result: 500 (Apple sales)

  9. Sum sales for products containing "an"

    • Formula: =SUMIF(A2:A11, "*an*", B2:B11)

    • Result: 550 (Banana sales)

  10. Sum sales for products with exactly 6 letters

    • Formula: =SUMIF(A2:A11, "??????", B2:B11)

    • Result: 550 (Banana sales)

  11. Sum sales for products with sales greater than 150 and less than 400

    • Formula: =SUMIF(B2:B11, ">150") - SUMIF(B2:B11, ">=400")

    • Result: 900 (200 + 250 + 300 + 150)

  12. Sum sales for products except "Orange"

    • Formula: =SUMIF(A2:A11, "<>Orange", B2:B11)

    • Result: 950 (Apple + Banana sales)

  13. Sum sales for products with sales equal to 100

    • Formula: =SUMIF(B2:B11, 100)

    • Result: 200 (100 + 100)

  14. Sum sales for products with sales greater than the average sales

    • Formula: =SUMIF(B2:B11, ">" & AVERAGE(B2:B11))

    • Result: 1300 (200 + 250 + 300 + 400 + 350)

  15. Sum sales for products with sales less than the median sales

    • Formula: =SUMIF(B2:B11, "<" & MEDIAN(B2:B11))

    • Result: 250 (100 + 50 + 100)

  16. Sum sales for products with sales between 100 and 300

    • Formula: =SUMIFS(B2:B11, B2:B11, ">=100", B2:B11, "<=300")

    • Result: 1050 (100 + 200 + 150 + 250 + 100 + 200)

  17. Sum sales for products with sales greater than 200 and product is "Banana"

    • Formula: =SUMIFS(B2:B11, A2:A11, "Banana", B2:B11, ">200")

    • Result: 250 (Only 250 meets the criteria)

  18. Sum sales for products with sales less than 150 and product is "Apple"

    • Formula: =SUMIFS(B2:B11, A2:A11, "Apple", B2:B11, "<150")

    • Result: 150 (100 + 50)

  19. Sum sales for products with sales greater than or equal to 300

    • Formula: =SUMIF(B2:B11, ">=300")

    • Result: 1050 (300 + 400 + 350)

  20. Sum sales for products with sales not equal to 100 or 200

    • Formula: =SUMIFS(B2:B11, B2:B11, "<>100", B2:B11, "<>200")

    • Result: 1450 (Total sales - 100 - 200 - 100 - 200)

No comments:

Post a Comment