Sum of values between two numbers – SUMIFS – MS Excel

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.

SalesmanProductSales in $
AApple2000
BOrange1500
CApple2000
DApple100
EApple900
FApple3000
GOrange1600
HApple1100
IOrange1700
Apple1000
KApple1900

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

https://infojinx.com/2017/03/sum-date-excel-tips/

Exit mobile version