The SUMIF function in Excel is used to sum the values in a range that meet a specified condition. Below are 10 examples of how to use the SUMIF function, including sample data and formulas.
Example 1: Basic SUMIF
Data:
A B 1 Apple 10 2 Banana 20 3 Apple 30 4 Orange 40
Formula:
=SUMIF(A1:A4, "Apple", B1:B4)
Result: 40
Explanation: Sums the values in B1:B4 where the corresponding value in A1:A4 is "Apple".
Example 2: SUMIF with Numeric Condition
Data:
A B 1 Apple 10 2 Banana 20 3 Apple 30 4 Orange 40
Formula:
=SUMIF(B1:B4, ">20")
Result: 70
Explanation: Sums the values in B1:B4 that are greater than 20.
Example 3: SUMIF with Wildcard
Data:
A B 1 Apple 10 2 Banana 20 3 Apricot 30 4 Orange 40
Formula:
=SUMIF(A1:A4, "Ap*", B1:B4)
Result: 40
Explanation: Sums the values in B1:B4 where the corresponding value in A1:A4 starts with "Ap".
Example 4: SUMIF with Not Equal Condition
Data:
A B 1 Apple 10 2 Banana 20 3 Apple 30 4 Orange 40
Formula:
=SUMIF(A1:A4, "<>Apple", B1:B4)
Result: 60
Explanation: Sums the values in B1:B4 where the corresponding value in A1:A4 is not "Apple".
Example 5: SUMIF with Cell Reference
Data:
A B 1 Apple 10 2 Banana 20 3 Apple 30 4 Orange 40
Formula:
=SUMIF(A1:A4, D1, B1:B4)
Assuming D1 contains "Apple":
Result: 40
Explanation: Sums the values in B1:B4 where the corresponding value in A1:A4 matches the value in D1.
Example 6: SUMIF with Date Condition
Data:
A B 1 2023-10-01 100 2 2023-10-02 200 3 2023-10-03 300 4 2023-10-04 400
Formula:
=SUMIF(A1:A4, ">2023-10-02", B1:B4)
Result: 700
Explanation: Sums the values in B1:B4 where the corresponding date in A1:A4 is after 2023-10-02.
Example 7: SUMIF with Multiple Criteria (Using SUMIFS)
Data:
A B C 1 Apple 10 Red 2 Banana 20 Yellow 3 Apple 30 Green 4 Orange 40 Orange
Formula:
=SUMIFS(B1:B4, A1:A4, "Apple", C1:C4, "Red")
Result: 10
Explanation: Sums the values in B1:B4 where the corresponding value in A1:A4 is "Apple" and the value in C1:C4 is "Red".
Example 8: SUMIF with Text Condition
Data:
A B 1 Apple 10 2 Banana 20 3 Apple 30 4 Orange 40
Formula:
=SUMIF(A1:A4, "*na*", B1:B4)
Result: 20
Explanation: Sums the values in B1:B4 where the corresponding value in A1:A4 contains "na".
Example 9: SUMIF with Logical Operators
Data:
A B 1 Apple 10 2 Banana 20 3 Apple 30 4 Orange 40
Formula:
=SUMIF(B1:B4, ">=30")
Result: 70
Explanation: Sums the values in B1:B4 that are greater than or equal to 30.
Example 10: SUMIF with Blank Cells
Data:
A B 1 Apple 10 2 Banana 3 Apple 30 4 Orange 40
Formula:
=SUMIF(B1:B4, "", A1:A4)
Result: 0
Explanation: Sums the values in A1:A4 where the corresponding value in B1:B4 is blank. In this case, there are no values in A1:A4 corresponding to blank cells in B1:B4.
These examples should give you a good understanding of how to use the SUMIF function in various scenarios.
No comments:
Post a Comment