Simplified Excel

Let’s say we want to sum if celll contain number i.e. adding those cells which are having sale amount of

1. Less than $1000;
2. Equal to $1000; &
3. More than $1000

In following sales register (Pic1)

Formula

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

Explanation

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

1. Sum if less than

=Sumif(F3:F8,”<1000”)

Output : $1384.62 (Added cells which are having sales of less than $1000)

2. Sum if equal to

=Sumif(F3:F8,”=1000”)

Output : $1000 (Added cells which are having sales of equal to $1000)

3. Sum if Greater than

=Sumif(F3:F8,”>1000”)

Output : $9000 (Added cells which are having sales of more than $1000)

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 2 : 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?

Example 3 : 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.