What is SUMIF and SUMIFS Formula in Excel??

We know that Microsoft Excel has hundreds of Built-In Formulas (Functions), And I have already share you some of the most built-in functions in Microsoft Excel.You can check it here

VBA's Most Useful Built-in Functions

So in this post I'm going to give you information about SUMIF and SUMIFS Formulas.
SUMIF and SUMIFS formula will give you some advanced skill in excel for users.

So What is the Use of SUMIF formula?????

It Sums items in a list matching a condition

Examples of SUMIF Formula

                     sumif(A1:A10, 10) = sums the cells with a value of "3" 

In this example you can see the sum of Value (Criteria) '10' in the Cell A11.

sumif(A1:A10, 10, b1:b10) = same as above but adds values in B1:B10

In this example you the corresponding cells in 'B' column will be added based on our criteria ie, 10

So it will add the B2, B5, B10 columns i.e. 11 + 14 + 19 = 44 

Syntax of SUMIF Formula

                    sumif(range, values meeting the criteria, [sum this range])

                    - [] optional

Consider a situation like you need find sales of apple from the below data.

You can use SUMIF like this

                          = SUMIF(A2:A10,"Apple",C3:C10)

But SUMIF has some limitation we can use it only for one condition at a time, consider a situation like you need to find how many Apples sold in India.

So this will be a complex task like either you need to put filter and find out the country INDIA and sum the sales or you need to use SUMPRODUCT formula. But the problem with SUMPRODUCT formlua is that you need to write some complex formulas there. 

so How can we implement SUMIFS formula here??????

From the name of the formula itself we can find that SUMIFS is plural or SUMIF, which mean if we can use SUMIF for single condition we can use SUMIFS for multiple conditions!!!!!!!!!! yeah gotcha!!!

So to get the sales of Apple in India ,

We need to write

                             = SUMIFS(A2:A10,C2:C10,”Apple”,B2:B10,”India”)


                                                                                                                                                                                                

                               Example showing the sales of apple in India

so How Does SUMIFS Works??????

SUMIFS formula takes a range for summing the values and at least one criteria range and criteria. You can specify as many as 127 conditions for summing your data!!!!!!!!!!!!.

Imagine asking “how many apples Mr.X sold in Bigtan of Planet Nibiru!!  between long long ago and long ago that resulted in more than 25% profits?” and getting an instant answer.

The beauty of SUMIFS formula is that it works with wildcards too, just like its siblings – SUMIF and COUNTIF. So you can write formulas like,

Consider you need to find the sales of apples in India and Australia

you can simply write SUMIFS formula like this

                     = SUMIFS(A2:A10,C2:C10,”Apple”,B2:B10,”*ia”)

Since India and Australia having the same trailing alphabets you can easily find the answer of that query!!!!!!

Any Exception for SUMIFS???????

Yes you are right, SUMIFS only work in Microsoft Excel 2007 or Above.
No Worries since all are having Windows 7 and higher version all can easily experience the power of SUMIF and SUMIFS.

And just like SUMIFs there is AVERAGEIFS and COUNIFS in excel 2007 + . So guys you can try those and comment the experience.

If you face any issues comment it!!!!!!!!!!!!!!

Good Night All!!!!!!!!!!!!!!!!!