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