###### Excel

#### Top 40 Microsoft Excel Interview Questions & Answers

Download PDF 1) What is Microsoft Excel? Microsoft Excel is an electronic spreadsheet application...

- Details

SUMIF is the function used to sum the values according to a single criterion. Using this function, you can find the sum of numbers applying a condition within a range. This function comes under Math & Trigonometry functions. Similar to the name, this will sum if the criteria given is satisfied. This function is used to find the sum of particular numbers within a large data set.

In this tutorial, you will learn-

- What is SUMIF Function?
- How to use the SUMIF function?
- Formula of MS SUMIF
- Example 1: Using SUMIF
- Example 2: How to use SUMIF with comparison operators
- Example 3: How to use SUMIF with dates

SUMIF will sum the range of cells according to the criteria specified. The range of cells to where the condition should be applied, the constraint according to that the sum will calculate, the range of cell for which the sum to calculate according to the condition need specify within the formula.

Generally, follow a format where the criteria and criteria range, etc. need to be provided.

SUMIF(range,criteria,sum range)

**Range**: The range of cells which included the criteria**Criteria**: The condition that must be satisfied**Sum range:**The range of cells to add if the condition is satisfied.

Given the data table with order numbers and the bill amount. Need to find the sum of the bill amount for particular orders.

**Step1)** You want to find the sum of the bill amount for a particular brand order where the order number starts with 'Amzcd.'

S# | Order No | Bill Amount |

1 | Amzcd 008 | $450 |

2 | Amzcd 009 | $500 |

3 | AFJK 010 | $1,102 |

4 | Amzcd 011 | $850 |

5 | Amzcd 012 | $564 |

6 | ABVF 013 | $345 |

7 | Amzcd 014 | $10.50 |

8 | Amzcd 015 | $835.50 |

9 | ABVF 016 | $1,236 |

10 | AFJK 017 | $478.20 |

11 | Amzcd 018 | $100.25 |

**Step 2)** Select the cell below to the bill amount and apply the formula to find the sum of bill amount only for the orders, which starts with 'Amzcd.'

**Step 3)** The formula applied is

'=SUMIF (B2: B12, "Amzcd*," C2: C12)'

- B2: B12 is the range of cell where the criteria will be checking.
- 'Amzcd*' is the criteria applied where the starting of the order number.
- C2: C12 is the range of cells to add according to the criteria

**Step 4)** If you check the table, highlighted are the bill amounts summed according to the criteria given. The bill amount for every order starts with 'Amzcd' are selected, and the sum will find.

**Step 5)** Check the end of the column bill amount to see the sum of the bill amount, which satisfies the condition given.

A set of comparison operators exists in excel. The SUMIF function can use along with comparison operators. Comparison operators can use to specifying the conditions within the SUMIF function. So if the comparison operation returns a true value, it will sum up the values.

**Step 1)** You have the table of data with name products, Qty, and price. How can you find the total price for the product for which the Qty is greater than and equal to 100?

S# | Product | Qty | Price |

1 | Soft drinks | 100 | $1,789 |

2 | Dress Materials | 80 | $752 |

3 | Edible Oil | 150 | $489 |

4 | Nuts | 500 | $2,589 |

5 | Cookies | 205 | $1,500 |

6 | Footwear | 130 | $300 |

7 | Electronic Items | 100 | $4,500 |

8 | vegetables | 410 | $3,752 |

9 | Toys | 50 | $200 |

10 | Furniture | 210 | $500 |

**Step 2)** If you apply the criteria into logical format 'Qty>=100', and the SUMIF formula can form as below.

**Step 3)** The formula applied is

=SUMIF (C2: C11,">=100", D2: D11)

- C2: C11 is the range of cell where the criteria will be looking for
- '>=100' is the condition which expressed with comparison operator greater than and equal to
- D2: D11 is the range to be summed according to the criteria

**Step 4)** Highlighted cells are the prices which satisfy the criteria, where the Qty>=100

**Step 5)** The sum of values are in the cell D13; it is the sum of prices for the products which have Qty greater than or equal to 100.

In this example, you will learn how to use SUMIF function with the date. The criteria can also give in the form of date.

**Step 1)** The table shows different account numbers, bill dates, and payment. The amount needs to be paid to the corresponding account within the bill date given. By using the SUMIF, you can try to find the sum of the amount to be paid for June.

S# | Account Number | Bill Date | Payment |

1 | 9160250011660588 | 1/4/2019 | 25000 |

2 | 9160250011689568 | 1/28/2019 | 15000 |

3 | 9160250012356588 | 1/31/2019 | 8500 |

4 | 9160250899560588 | 6/2/2019 | 9635 |

5 | 9160260231660588 | 2/10/2019 | 4582 |

6 | 9160250011658588 | 6/25/2019 | 10265 |

7 | 9160457811660588 | 3/1/2019 | 8132 |

8 | 9160250011670858 | 3/12/2019 | 9562 |

9 | 9160250011663569 | 6/15/2019 | 7895 |

10 | 9160250321660456 | 6/18/2019 | 2566 |

**Step 2)** Since the date is with you, the criteria can form as '>5/31/2019' which refer to the dates after May. Here the comparison operator and date are using at a time with SUMIF.

**Step 3)** The formula applied is

=SUMIF (C2: C11,">5/31/2019", D2: D11)

- C2: C11 is the range of cells where the dates are given and are the range where given criteria will be looking for
- '>5/31/2019' is the criteria where date greater than '5/31/2019'
- D2: D11 range of numbers to add once the condition is satisfied

**Step 4)** Below are the cell values which satisfies the applied condition, and the sum of these values will find.

**Step 5)** The sum is there in D13. And is the total amount to be paid to multiple accounts for June.

Download the above Excel Data File

- SUMIF function will make the sum of values according to criteria given.
- SUMIF function is compatible with numeric, date, text data
- Conditions can express from the result of other functions or operators
- SUMIF function can be applied only for a single column at a time
- A unique condition is allowed to check with SUMIF function
- #VALUE will be an error result when the range does not match with criteria range
- Part of the word or sentence will be matched using a wildcard. Asterisks symbol use with a series of characters and question mark for a single character.
- While using text along with numeric values, the criteria should enclose within a double quotation, but not if it consists only of a numeric value.

Download PDF 1) What is Microsoft Excel? Microsoft Excel is an electronic spreadsheet application...

What is VLOOKUP? Vlookup (V stands for 'Vertical') is an in-built function in excel which allows...

Microsoft Excel is a spreadsheet program that is used to record and analyze numerical data. Excel...

In this tutorial, we are going to import data from a simple external database powered by Microsoft Access...

There will be times when you will be required to analyse large amounts of data and produce easy to...

Excel is a spreadsheet software included in the Microsoft office suite. It widely is used to...