Let’s say we want to sum if celll contain number i.e. adding those cells which are having sale amount of
- Less than $1000;
- Equal to $1000; &
- More than $1000
In following sales register (Pic1)
= Sumif (range, criteria, [sum range])
To sum the cells having numbers or amounts, use sumif function with logical operators like <,>,>=,<= and =.
1. Sum if less than
Output : $1384.62 (Added cells which are having sales of less than $1000)
2. Sum if equal to
Output : $1000 (Added cells which are having sales of equal to $1000)
3. Sum if Greater than
Output : $9000 (Added cells which are having sales of more than $1000)
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.
Example 2 : 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 3 : 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.