To add number in excel which lies between two number, SUMIFS function of MS can be used. SUMIFS function is used to add data which meets multiple conditions or criteria’s.
Syntax
=SUMIFS(sum_range, criteria_test_range_1, criteria_1, criteria_test_range_2, criteria_2,...........)
Where
sum_range is range of cell where number’s need to be added are placed.
criteria_test_range is range of cells where test criteria is placed.
criteria is condition on which sum range is to be added.
Example
We have a sample of data where details of sales for apples and oranges by different salesman is given.
| Salesman | Product | Sales in $ |
| A | Apple | 2000 |
| B | Orange | 1500 |
| C | Apple | 2000 |
| D | Apple | 100 |
| E | Apple | 900 |
| F | Apple | 3000 |
| G | Orange | 1600 |
| H | Apple | 1100 |
| I | Orange | 1700 |
| J | Apple | 1000 |
| K | Apple | 1900 |
SUMIFS – example – use 1
First we will find all sales made for value between $1500 to $2000
Formula we will use is
=SUMIFS(C2:C12,C2:C12,"<2000",C2:C12,">1500",B2:B12,"=Apple")
Note: criteria <2000 and >1500 will exclude 1500 and 2000. if you want to include these two numbers then use <=2000 and >=1500.
SUMIFS – example – use 2
Now we will find all sales made for apples with value between $1500 to $2000
Formula we will use is
=SUMIFS(C2:C12,C2:C12,"<2000",C2:C12,">1500",B2:B12,"=Apple")
Another example use SUMIFS command is in below link where we added sales between two dates
