Thursday, April 23, 2020

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.