Usage of SUMIF & SUMIFS in Excel

Today we will cover two basic yet very essential functions of MS Excel.

1. SUMIF &
2. SUMIFS

We believe, you already know the functionality of sum function. If you don’t know, please check out this SUM function tutorial.

SUMIF and SUMIFS have the same functionality likewise a SUM function but these two functions contain additional criteria to lookup the entire data table and based on those criteria it performs the sum operation.

Keep in mind, SUMIF function accepts only one criteria whereas SUMIFS function takes multiple criteria.

Introduction of SUMIF

SUMIF comes up with a comparison operator like greater than, less than or equal, etc, It supports both character and integer.

When there are a large number of data set their manual calculation is ineffective when you have to find out the range of sum based on condition. 

General syntax

=SUMIF (range, criteria, [sum_range])

Example & Working way,

Just think of a biscuit company inventory, where you need to find out how many kgs of flour ingredients is available there. So you must need a condition to apply in the sum that only flour ingredients need to sum up. Therefore, SUMIF function you can infuse a condition on SUM.

Now, for clear understanding, follow the below steps carefully.

1. Try to create or just copy & paste the below dataset on your excel worksheet as an example.

Product	Region	salesRep	Customer	Sales
bellen	South	zubbar	e	123
sun	East	zubbar	a	234
bellen	west	zubbar	g	345
sun	north	zubbar	w	456
zasa	East	zubbar	e	567
bellen	west	zubbar	e	678
zisa	north	zubbar	a	789
bellen	west	zubbar	g	344
sun	north	zubbar	w	1222
mss	East	akib	e	2100
zasa	west	akib	e	2978
bellen	East	akib	a	3856
sun	west	akib	g	4734

2. Then choose a blank cell and type “=SUMIF (A2:A14,"bellen", E1:E14)” to calculate the sum of bellen product sales and you will get a result of 4568 Sales based on the criteria.

Then, try to work with other products from the dataset to calculate the sum of the sales of those products. Hopefully, it will give you a clear concept, how actually SUMIF function work in MS-Excel.

Introduction of SUMIFS

SUMIFS function is almost the same as SUMIF. It has the power where you can set multiple conditions or on excel sense criteria.

General syntax

=SUMIFS (sum_range, range1, criteria1, [range2], [criteria2], ...)

Example & Working way,

1. Again, copy & paste the given dataset on your excel worksheet or prepare one by yourself.

Product	Region	salesRep	Customer	Sales
bellen	South	zubbar	e	123
sun	East	zubbar	a	234
bellen	west	zubbar	g	345
sun	north	zubbar	w	456
zasa	East	zubbar	e	567
bellen	west	zubbar	e	678
zisa	north	zubbar	a	789
bellen	west	zubbar	g	344
sun	north	zubbar	w	1222
mss	East	akib	e	2100
zasa	west	akib	e	2978
bellen	East	akib	a	3856
sun	west	akib	g	4734

2. Choose a blank cell where you want to show your sum result and start typing “=SUMIFS(E2:E14,A2:A14,"bellen",C2:C14,"zubbar")” to calculate the total sales of the product bellen which is sold under a salesRep named zubbar. You will get a result of 1490 Sales based on those criteria.

Hopefully, this tutorial makes some sense and got a clear understanding, how actually SUMIF & SUMIFS function work and also know the difference between them.

That’s set for today. If you have any inquiries or suggestions regarding this please write to us in the comment section. Stay with tutorialabc for more easy excel tutorials.