Simplified Excel

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?

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 :

1. Non- numeric criteria has to be written with double inverted comma (e.g. “Rahul”)
2. 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.
3. 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

1. Through Agent
2. Without Agent

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

Let’s see

Example 4 : Sum if cells are blank

Here we have two option,

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

Click here to read more.