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)
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.
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?
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.