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:

A	B
Product	Sales
Apple	100
Banana	200
Apple	150
Orange	300
Banana	250
Apple	50
Orange	400
Banana	100
Apple	200
Orange	350

---

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)