Excel Sumifs Formula
SUMIF function adds the numbers based on a single criterion. For example, adding the sales total for a particular product will be done through the use of SUMIF formula. We have covered it in the previous article which you can find at Sumif function.
SUMIFs take it to another level, means numbers are added based on multiple criteria. For example, adding sales total for a particular product in a particular region will be performed through SUMIFs formula. Here the criteria are Product and Region.
The formula for this function is:
=SUMIFS(sum range, criteria 1 range, criteria 1, Criteria 2 range, criteria 2). Assume we have the following data and our aim is to arrive at total sales amount made by each team product wise.
We have the below sales data for Sales Teams and Products. The objective is to have a calculation in cell H4 of total sales for sales Team A for Product AD-99C. Once we have the data for the Sales Team A, you can then change the Sales Team and the total will be updated instantly.
Enter the following formula in cell H4 = SUMIFS($D$4:$D$15,$B$4:$B$15,F4,$C$4:$C$15,G4)
The final output is our required results.
you can play with it by changing the product Name to B in cell 4 and you can change the name of the product in G4. To download the file, please click here.
Sumifs Excel Functions: How to add numbers between Two dates
Let’s take an example, where you need to prepare two reports one from 1st to mid of the month and then from 16th till the end of the month. Another point to note that both the reports are based on a more than one criteria. How will you go about? Let’s this important skills. Our data is provided below:
We have the sales for the whole year for products (Fruits & Vegetables). We want to come up with the sales for product Vegetable for the month of September. Pay attention and you will learn the useful trick.
In above examples, we have 3 criteria:
- Dates from 1st to 15
- Dates from 16 till the end of the month
- In this formula: Sum Range is sales Column
- Products column is representing criteria 1 range.
- Criteria 1 is Product (in our case it’s vegetables)
- Dates column is representing criteria 2 Range
- Criteria 2 is “>=”&G5, this will find sales amounts that are done either on this date and on the coming dates
- “Dates” column again represents criteria 3 range.
- Criteria 3 is “<=”&H5, this will extract the sales made on this date and on the past dates.
- Both of the criteria 2 and 3, make sure that sales calculated fall between these dates. i.e 1st to 15.
- Close the brackets and hit enter and you are done.
Change the dates and see if your calculation updates. Cheers, you are having another excel skill at your disposal. Share it with your friends and colleagues.
Download the file from here.