How to use sumif in excel

Answer

SUMIF is a function in Excel that adds the values in a range that meet a specific condition. It is used to perform conditional summing without manually filtering or calculating values.

Syntax of SUMIF
The basic syntax is:

SUMIF(range, criteria, [sum_range])

1. range – The cells to evaluate against the criteria.

2. criteria – The condition that determines which cells to sum. This can be a number, text, expression, or even a cell reference.

3. sum_range – Optional. The actual cells to sum. If omitted, Excel sums the cells in the range itself.

How to Use SUMIF in Excel

1. Sum values based on a number condition
   To sum sales greater than 500 in column B:

   =SUMIF(B2:B10, ">500")

   Excel adds all numbers in B2:B10 that are greater than 500.

2. Sum values based on text criteria
   To sum sales where the region is "East" in column A, and the sales values are in column B:

   =SUMIF(A2:A10, "East", B2:B10)

   Excel checks each cell in A2:A10 for "East" and sums the corresponding values in B2:B10.

3. Sum values using a cell reference for criteria
   Suppose D1 contains the value "West", and you want to sum sales for "West":

   =SUMIF(A2:A10, D1, B2:B10)

   Excel dynamically uses the value in D1 as the condition.

4. Sum values with comparison operators and cell references
   To sum sales greater than the value in D2:

   =SUMIF(B2:B10, ">" & D2)

   Excel concatenates the operator with the cell reference to apply the condition.

Important Notes

1. SUMIF works for a single condition. To use multiple conditions, use SUMIFS.

2. Wildcards can be used in text criteria:

   • ? matches any single character.

   • * matches any sequence of characters.
     Example:

     =SUMIF(A2:A10, "E*", B2:B10)

   Sums values where column A starts with "E".

3. SUMIF ignores empty cells and text in the sum_range if the criteria is numeric.

4. On Mac, the formula works identically to Windows Excel. Differences appear only in interface navigation, not in formula syntax.

Example Scenario

• Formula:

  =SUMIF(A2:A5, "East", B2:B5)

• Result: 400 + 700 = 1100

This sums only the sales values where the region is "East".

Yes · 0 No · 0 Add Comment · 0

Add ·

Report