How Excel SUMIFS Can Ease Your Work: A Tutorial

The SUMIFS function is a built-in function that sums cells in a range by using supplied criteria. This function can apply multiple criteria with more than one range. It enables users to sum values when interrelated cells meet criteria. This is usuallybased on numbers, dates, and text.

The beauty of excel SUMIFS is that it works with logical operators, which are >, <, <>, and =. SUMIFS is also in support of wildcards (*, ?) for partial matching.

When the SUMIFS function gets triggered, users need to add parameters. The following are the parameters the SUMIFS function accepts:

• Sum_range: It’s the range of data whose sum a user would like to find.
• Criteria_range: It’s a range of cells used as a filter by applying criteria1 against the data in them.
• Criteria 1: It’s the criteria that the range of cells in criteria_range1 are filtered by. That is before summing up the values.
• Criteria2,….criteria_n: It helps users in deciding which cells to add.

Read: How to edit Excel spreadsheets on Google Drive

Using the SUMIFS Function in Excel

The SUMIFS function can be applied to several sets of criteria having more than one range. The following are the points to remember while using this function:

• Each additional range must have the same number of rows and columns denoted as sum_range.
• Non-numeric criteria must be enclosed in double-quotes. Numeric criteria don’t need to be enclosed in the doubled quotes.
• Users are at liberty to use Wildcard characters such as % or * in criteria.
• To find a literal question, use tilde(~) or asterisk.
• Be sure to only mention the ranges, not arrays.

The SUMIFS and SUMIF functions are quite similar. Thus, users can choose to use the SUMIF function. The only difference is that with SUMIF, users get to test only one condition. But, with the SUMIFS function, they can test up to 127 criteria.

You can apply SUMIF across columns that meet a specified criteria. For instance, assuming that in a column containing numbers, you want to sum values larger than 5. You can use the formula =SUMIF (B2:B25,”>5”).

Read: The Best Spreadsheet Software for Linux

Let’s now find out how the SUMIFS function can ease your work.

https://www.istockphoto.com/photo/hand-point-on-data-report-show-on-screen-gm635969404-112543171

Read: 7 Facts about Microsoft Office Course and Windows That You Never Knew

Using SUMIFS With Tables to Save Time

The SUMIFS function can be written on a range of data or a table. Users can simply apply structural references when using SUMIFS with tables. For instance,

TableName(column Name) notation to identify the criteria columns.

Assuming you have a store and you sell ray bow flashlights. We’ll create a table and name it Aki, to determine how many ray bow flashlights were sold. See the Excel SUMIFS examples below to know how the SUMIFS function works:

• Sales for ray bow flashlight in West

= SUMIFS (Aki(sales), Aki(sales),”ray bow flashlight,” Aki(Region), “West”)

• Sum total sales above 150 in East

=SUMIFS(Aki(sales), Aki(SALES), “>150,” Aki(Region), “East”)

• Sales of North for all factoring out pod Gun

=SUMIFS(Aki(sales), Aki(Region), “North,” Aki(product), “<>Pod Gun”)

• Sales of all the products with letter B

=SUMIFS(Aki(sales), Aki(product), “ *B*”)

The example above shows how easy it is to know how many products were sold in each region. It saves employees the time they would have used to manually filter the entries, and find the entry that fits their criteria.

Read: How to speed up your Windows computer

Using SUMIFS to Provide Accurate Results

When it comes to columns of dates, users can apply special operators such as >, <, =, <> when specifying a date range. For instance, to count the total sales between March 2021 and May 2021, with our earlier example, we can use:

• =SUMIFS (Aki(sales), Aki(sales Date), “>=1 –Mar-2021,” Aki(sales Date),”<=31-May-2021”)

We can type the date in the formula or bring it from a cell. Assuming we have two cells with start and end dates for our window of dates, we would use the formula below:

• =SUMIFS(Aki(sales), Aki(sales Date), “>=”&start_date_cell, Aki(sales Date), “<=”& end_date_cell

The Excel SUMIFS function in this case helps you to set the time and date you’d want to look into.

Note that you can replace start_date_cell and end_date_cell with the actual cell names.

Using a Set of Range and Criteria in SUMIFS

Let’s use SUMIFS to figure out all the hammers a store sold for the day is the example we’ll use here.

A user needs to choose the cell they’d like the answer to appear in first. For instance, you can choose cell D19 and click on the fX button. The Formula Builder will appear on the right-hand side of the Excel interface.

The SUMIFS function is part of the Math & Trig function in Excel. A user can choose Math&Trig, then click on the SUMIFS function. They can also opt for searching in the Formula Builder with “SUMIFS,” then click on the function. This action will open up a wizard. The next step would be using the wizard to enter the criteria for the function.

The task here is to find the total of all the hammers every staff member sold. First, set the sum_range to the cell references D2:D17. Thereafter, look at the amount column to know the number of hammers sold by each member of staff.

Now you know how to set criteria_range1 as B2:B17. If you type “Hammer” into criteria1, Excel will search for each entry where “Hammers” appear. Then the number of hammers sold by every member of staff will be summed up.

Read: How to speed ​​up Windows 10 shutdown

Using Two Sets of Range and Criteria in SUMIFS

Here, we’ll use two parameters in our example. You need to tell Excel to SUM all your nail orders. Using the SUMIFS function, choose your target cell first. The next step is for you to select the formula tab.

Choose the Math&Trig functions, then choose SUMIFS from the drop-down menu. Here, the SUMIFS function wizard will open as well. This will enable you to generate the criteria for your SUMIFS function.

The sum_range is the units sold since you want Excel to count how many nails you sold. The first criteria range is the type of stock. The criteria to search for this time around is “Nail.”

The second criteria you should have Excel search for is the salesman criteria. The range is C2:C17, and the criteria you’ll need to enter is your name. Excel will then SUM all the nail units that you sold.

Read: Which Version of Windows 10 Is Right for You

Conclusion

The Excel SUMIFS function is very powerful. That can be attributed to the fact that it can work on both single criteria and multiple criteria.The SUMIFS function can perform with the wildcards i.e, a question mark (?) or an asterisk(*)

It also allows users to get the summation values between two dates. On that basis, users can calculate the summation between months or even years. Nested with the EOMONTH function, the dynamic formula allows for the summation of a particular month from a range of dates. The multiple SUMIFS functions save users a lot of time in their daily use of Excel. They’re able to avoid applying different SUMIFS formulas in different cells.