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
Sum sales for "Apple"
Formula:
=SUMIF(A2:A11, "Apple", B2:B11)
Result:
500
(100 + 150 + 50 + 200)
Sum sales for "Banana"
Formula:
=SUMIF(A2:A11, "Banana", B2:B11)
Result:
550
(200 + 250 + 100)
Sum sales for "Orange"
Formula:
=SUMIF(A2:A11, "Orange", B2:B11)
Result:
1050
(300 + 400 + 350)
Sum sales greater than 200
Formula:
=SUMIF(B2:B11, ">200")
Result:
1650
(250 + 300 + 400 + 350 + 200 + 150)
Sum sales less than or equal to 100
Formula:
=SUMIF(B2:B11, "<=100")
Result:
250
(100 + 50 + 100)
Sum sales not equal to 200
Formula:
=SUMIF(B2:B11, "<>200")
Result:
1800
(Total sales - 200)
Sum sales for products starting with "A"
Formula:
=SUMIF(A2:A11, "A*", B2:B11)
Result:
500
(Apple sales)
Sum sales for products ending with "e"
Formula:
=SUMIF(A2:A11, "*e", B2:B11)
Result:
500
(Apple sales)
Sum sales for products containing "an"
Formula:
=SUMIF(A2:A11, "*an*", B2:B11)
Result:
550
(Banana sales)
Sum sales for products with exactly 6 letters
Formula:
=SUMIF(A2:A11, "??????", B2:B11)
Result:
550
(Banana sales)
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)
Sum sales for products except "Orange"
Formula:
=SUMIF(A2:A11, "<>Orange", B2:B11)
Result:
950
(Apple + Banana sales)
Sum sales for products with sales equal to 100
Formula:
=SUMIF(B2:B11, 100)
Result:
200
(100 + 100)
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)
Sum sales for products with sales less than the median sales
Formula:
=SUMIF(B2:B11, "<" & MEDIAN(B2:B11))
Result:
250
(100 + 50 + 100)
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)
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)
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)
Sum sales for products with sales greater than or equal to 300
Formula:
=SUMIF(B2:B11, ">=300")
Result:
1050
(300 + 400 + 350)
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