The difference between SUMIF and SUMIFS is that SUMIF function sums a range on single condition and the SUMIFS function can sum values on multiple conditions.
Sum_range : The array that you want to sum (must be numeric)
Criteria_range1 : The range of your first condition.
Criteria1 : The condition from Criteria_range1
[Criteria_range2] : The range of your second condition.
[Criteria2] : The condition from Criteria_range2
SUMIFS function works with range-criteria pair. First range-condition pair is mandatory.
Other range-condition pairs are optional but if you give a range than condition is mandatory. Let’s see an example.
Lets take the example of a data from stationary store. See the image below.
Now we need to get some of Cost of Central region, where Cost is more than 300.
Write this SUMIFS formula in excel 2016, 2013, 2010 or older. Its available in all versions of Excel running now.
$E$2:$E$16 : This is our sum range. I have used absolute referencing here using $ sign. It locks your range and does not change when copied to other cell.
$A$2:$A$16 : The first criteria range 1. Used absolute referencing here too.
G2: its the reference to criteria. G2 contains central, it means “central” is criteria.
$E$2:$E$16: Its the criteria range 2.
H2: It is the reference to criteria which is “>300”
Its done. You can see the sum of Cost from Cenral Region whose cost is more than 300 in cell I2. Its 4765.17.
If you want sum of pencil units sold to gill than write this SUMIFS formula
Sum of units sold to in central to Andrews that are 20 units
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.