SUMIFS - Advance Excel

This article is inspired by my recent conversion with interviewer where he had asked after detailed technical round “Do you know Microsoft Excel and SUMIFS function”.

As usual I said yes I know.

And then I thought, is SUMIFS so important function in Microsoft Excel? And if it is then I should write a detailed article on SUMIFS function.

In this article I will try to explain SUMIFS function in 5 different scenario from basic to advance.

For this Article you need to first download Microsoft Excel workbook of Sales Information of M/s XYZ Limited for the period of April-14 to March-16 (attach herewith).

But before I proceed, I need to explain SUMIFS briefly how SUMIFS function works.

SUMIFS formula is basically work on conditional sum, which can be expand with n times of conditions. Unlike SUMIF formula which restrict itself only to one condition.

SUMIF FUNCTION works in this way
SUMIFS (Sum_Range, Criteria_Range1, Criteria1, [Criteria_Range2, Criteria2], ...)

The SUMIFS function syntax has the following arguments

Sum_range:  Required, One or more cells to sum, including numbers or names, ranges, or cell references  that contain numbers. Blank and text values are ignored.

Criteria_range1:  Required, The first range in which to evaluate the associated criteria.

Criteria1: Required, The criteria in the form of a number, expression, cell reference, or text that define which cells in the Criteria_range1 argument will be added.

SCENARIO 1 - NEED TO GET SUM OF ALL SALES MADE BY "ABHISHEK".

To get all sales made by Abhishek you need to just right simple SUMIFS Function
=SUMIFS(H5:H503,C5:C503,U7)

But wait we can write this formula in better way in understandable manner.
=SUMIFS(Sales_Amt,Sales_Person,U7)

You might ask how I did this, using name than ranges!!

Well, if you remember my article on Two Way Lookup I did the same thing. In my previous article Two Way Lookup in Microsoft Excel where I created names for all numeric data values with their Top headings.

Same thing I also done here to create name of all data values with their respective headings.

If you are still confused let me repeat those three steps one again.

Steps 1 Select entire table (Short cut combination Ctrl + A) 

Step 2 Go to the “Create from Selection” under “FORMALAS” tab (Short cut combination Ctrl +Shift + F3)

Step 3 Ensure that Top row is checked and click OK

Now while using SUMIFS write =SUMIFS(

Then press F3 (short cut key to display all names we created) and select Sales_Amt (Sum_Range) and press okay followed by, then again press F3 and select Sales_Person (Criteria_Range) followed by, and U7 (Criteria).

Instantly you will get same result with the use of name than range and, using meaningful range (Sales_Amt) is much more easier to remember than a range address (such as C5:C503) as well as make easier to use and understandable while using these name in formulas.

SCENARIO - 2 GET SUM OF ALL SALES MADE BY "ABHISHEK" FOR PRODUCT "HARDWARE MODEL C".

For this scenario we just need to expand our formula by adding one more criteria range (Product) and criteria (Hardware Model C)

=SUMIFS(Sales_Amt,Sales_Person,U7,Product,U8)

SCENARIO - 3 GET SUM OF ALL SALES MADE BY "ABHISHEK" FOR PRODUCT "HARDWARE MODEL C" IN "SOUTH" REGION.

Again we just need to expand our formula by adding one more criteria range (Region) and criteria (South)

=SUMIFS(Sales_Amt,Sales_Person,U7,Product,U8,Region,U9)

SCENARIO - 4 GET SUM OF ALL SALES MADE BY "ABHISHEK" FOR THE PERIOD "APRIL 15 TO MARCH 16".

This is tricky, in all other previous scenarios we have to get calculate sum based on single criteria but in this case we need to compute sum on the basis of range.

And for these types of problems we need to understand comparatives operators of Microsoft Excel.

Now come back to our Scenario 4, where we have to need a sum of all sales made by Abhishek during the period of April15 to March16.

In this case we need to use comparative operator “>=” greater or equal before 01-04-2015, since we need information of all sales made on or after 01-04-2015. And “<=” less than or equal to before 31-03-2016, since we don’t want to have any information of sales made on after 31-03-2016.

And or formula will be
=SUMIFS(Sales_Amt,Sales_Person,U7,Date,U8,Date,U9)

SCENARIO - 5 GET SUM OF ALL SALES MADE BY "ABHISHEK OTHER THAN "PRODUCT HARDWARE MODEL C".

This again requires comparative operator “<>” does not equal to product Hardware Model C, since we don’t want sales information of “Hardware Model C”. Therefore we need to use “<>” to validate our conditions.

And our formula will be
=SUMIFS(Sales_Amt,Sales_Person,U7,Product,U8)

There are N number of situation we can create, which we generally have to face in our daily routine office work. And you might have your own short cuts and tricks to deal with these situations, but when you are aware and regularly using these comparative operators in between normal Microsoft Excel formulae, you will start playing with Microsoft Excel or sometime you will able to write your own Complex Excel Formulae.  And important thing is theses comparative operations not only in SUMIFS Function, you may use in SUMIF, COUNTIF, COUNTIFS AND EVEN IN VLOOKUP too. You need to just try.  

Anyway, now I can assume you all are aware about how to use these comparative operations, so that I could avoid to explain these in my future articles.

Hope you all are gain something from this article and if we have any query regarding this article or anything regarding Microsoft Excel feel free to comment or get in touch.

The author can also be reached at camukeshkumar@outlook.com

You may also make reference to my other Articles on Advance Excel Function.

Two way lookup in Microsoft Excel - Advance Excel
Extracting data in Microsoft Excel - Advance Filter
Ageing report from pivot table - Advance Excel
Vlookup's Advance Function (Advance Excel)
Data Consolidation in Microsoft Excel Part-2 (Advance Excel)
Data Consolidation in Microsoft Excel (Advance Excel)
Linking Tally with Excel Steps (Advance Excel)
Scenario Manager in Excel (Revised)
Data Protection In Microsoft-Office