Simplified Excel

Sumif function of excel is one of most useful function if used properly. Let’s use sumif function to add cells which are not blank.

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

1. Through Agent
2. Without Agent

In following sales register (Pic1)

Formula

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

Explanation

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

Let’s see

First let’s do,

1. Sum if cells are not blank

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

=sumif(C3:C8,”<>”,G3:G8)

Output: $8169.23 (Sales made through Agents only)

C3:C8 – Range of cells – Agent

“<>” – Criteria – This is symbol of non-empty cells.

G3:G8 – Sum Range – To be added

2. 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.

In our example,

1. We can reduce the sales without agent from total sales;

But what if I didn’t find that and I need to sum this directy.

No Worries, we have sumif function for that also.

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

=sumif(C3:C8,””,G3:G8)

Output: $3215.38 (Sales made without Agents)

C3:C8 – Range of cells – Agent

“” – Criteria – This is symbol of empty cells.

G3:G8 – Sum Range – To be added

Related Function:

How does a Sumif work in Excel?

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

Related Examples:

Example 3 : Sum if cell contain number

To sum the cells having numbers or amounts, use sumif function with logical operators like <,>,>=,<= and =.