Sum function of excel add the cells which are selected by cell reference, but what if I want to add the cells which meet the specific criteria. The criteria for adding up cells could be text, partial text, number etc.

“Always wondering that How to sum specific cells in excel?”

“Do you Want to Sum if cell contains text?”’

“Do you Want to Sum if cell contains number?”

Before jumping to sumif function and its arguments, let’s understand what is sumif?

Contents

- 1 What is Sumif?
- 2 Location of Sumif :
- 3 Syntax of the function:
- 4 Arguments of Sumif
- 5 Problem 1 : We want to sum of the sales made to Parth Patel in given sales register: ( Example of sum if cell contains specific text) (Pic1)
- 6 Solution 1 : Sum if cell contain text
- 7 Points to be considered while using sumif :
- 8 Practical Examples of Sumif Function
- 9 Example 2 : Sum if cell contain number
- 10 Example 3 : Sum if cell are not blank
- 11 Example 4 : Sum if cells are blank

**What is Sumif?**

Sumif function of excel adds the cell which meets specific condition or criteria like adding cell which containts name “Rahul” or adding amount which is more than “>$500”

**Location of Sumif :**

Formulas Maths & Trig Sumif

No need to remember the location of any formula, you could just press “**=**” and write “sumif”, excel will automatically catch the function.

**Syntax of the function:**

= Sumif (range, criteria, [sum range])

**Arguments of Sumif **

Let’s understand each of 3 arguments of function one after another.

**Range :**The range of cell on which you want to apply specific criteria or**condition**.**Criteria :**Criteria to be applied on range of cells. (e.g.**“Rahul”**)**Sum Range :**(It is an optional argument. In the absence of input, sumif will add range of cells itself.)**Cells to be added**if range of cells meet criteria given.

**Problem 1 : We want to sum of the sales made to Parth Patel in given sales register: ( Example of sum if cell contains specific text) (Pic1)**

**Solution 1 : Sum if cell contain text**

= Sumif (range, criteria, [sum range])

= Sumif(B3:B8,”Parth Patel”,F3:F8)

Output : $ 3923.08

**B3:B8**– Range of cells which we want to evaluate based on criteria**“Parth Patel”**– Criteria specifying that we want to evaluate cells which contain text “Parth Patel”**F3:F8 –**Values to be summed for criteria fulfilled row.

**Points to be considered while using sumif :**

**Non- numeric criteria**has to be written with**double inverted comma**(e.g. “Rahul”)**Numeric**criteria can be written**without double inverted comma**. However when giving**logical arguments**to numeric criteria, one has to write**with**double inverted comma.**Wildcards**like**?**and*** can be used**in sumif formula. “**?**” stands for one character and “*****” stands for one or more characters.

**Practical Examples of Sumif Function**

**Example 2 : Sum if cell contain number**

To sum the cells having numbers or amounts, use sumif function with logical operators like <,>,>=,<= and =. **Click here to read more.**

**Example 3 : Sum if cell are not blank **

Let’s say we want to add up those cells which are sold

- Through Agent
- Without Agent

To sum the cells having cells which are not blank, sumif function can be used. How?

**Example 4 : Sum if cells are blank**

Here we have two option,

- First find out the total of cells which are not blank and then deduct the same from total sum.
- Directly sum if cells are blank.

## Leave a Comment