How to Use the SUMIF Function in Excel: A Step-by-Step Guide

Rick Akura
Learn how to use SUMIF function in Excel to quickly summarize data based on specific criteria. Step-by-step guide with examples.
How to Use the SUMIF Function in Excel

If you're working with large data sets in Excel, the SUMIF function can be an incredibly useful tool. 

In this step-by-step guide, we'll take you through everything you need to know about using the SUMIF function in Excel. 

By the end of this guide, you'll know how to use SUMIF to calculate the sum of values that meet specific criteria, including text, dates, and numbers. 

Table of Contents

  1. Summarizing Data with SUMIFS Function in Excel
  2. Summing Between Two Dates in Excel Using SUMIFS Formula
  3. Summing Data Within a Dynamic Date Range in Excel Using TODAY Function
  4. Excel SUMIFS Troubleshooting
  5. Using Data Analysis Toolpak in Excel
  6. Excel Pivot Tables
  7. Using Slicers for Excel Data Analysis
  8. FAQs
  9. Final Thoughts

Summarizing Data with SUMIFS Function in Excel

The SUMIFS function is a useful tool in Excel that allows you to add up values based on multiple criteria. This function enables you to summarize data in a way that is tailored to your specific needs. 

The SUMIFS function can conditionally sum values based on various criteria, including:

  • Numerical values
  • Dates
  • Text
  • Boolean expressions

For example:
You can use SUMIFS to sum up, sales figures for a specific product within a specific date range and from a specific sales region. Or you can use it to add up the number of hours a specific employee works on a specific project during a specific week. 

The possibilities are endless, and the SUMIFS function makes it easy to summarize data in a way that is both efficient and accurate.

Summing Between Two Dates in Excel Using SUMIFS Formula

To sum values in Excel between two dates, you can use a SUMIFS formula. This formula requires you to specify the dates and the sum range of values you want to add up. 

Here's an example table to demonstrate how to use the SUMIFS formula to sum values between two dates:

Summing Between Two Dates in Excel Using SUMIFS Formula

 

Let's say you want to sum the sales between January 5, 2023 and January 15, 2023. Here are the steps to follow using the SUMIFS formula:

  1. Select the cell where you want to display the result (e.g. cell B7).
  2. Type in the formula "=SUMIFS(" followed by the sum range of values you want to add up (e.g. B2:B6).
  3. Add a comma and specify the date range as the first criterion (e.g. A2:A6).
  4. After that, add another comma and type in the start date using the greater than or equal to (>=) operator (e.g. ">="&DATE(2023,1,5)).
  5. Add another comma and then specify the range of dates again as the second criterion (e.g. A2:A6).
  6. Add another comma and type the end date using the less than or equal to (<=) operator (e.g. "<="&DATE(2023,1,15)).
  7. Close the formula with a closing parenthesis ")" and press Enter.

Your final formula should look like this:

=SUMIFS(B2:B6, A2:A6, ">="&DATE(2023,1,5), A2:A6, "<="&DATE(2023,1,15))

The result will be 3,500, which is the sum of the sales between January 5, 2023 and January 15, 2023.

Summing Data Within a Dynamic Date Range in Excel Using TODAY Function

If you want to sum data within a dynamic date range based on today's date, you can use the TODAY function in Excel. The TODAY function gets the current date, and you can use it to update the date criteria in your formula automatically. 

Here's an example table to demonstrate how to use the TODAY function to sum data within a dynamic date range:

Summing Data Within a Dynamic Date Range in Excel Using TODAY Function

 

Let's say you want to sum the sales data for the last 7 days from today's date. Here are the steps to follow using the TODAY function:

  1. Select the cell where you want to display the result (e.g. cell B7).
  2. Type in the formula "=SUMIFS(" followed by the range of values you want to add up (e.g. B2:B6).
  3. Add a comma and specify the date range as the first criterion (e.g. A2:A6).
  4. Add another comma and then type in "<="&TODAY() to include the current date and the previous 7 days (e.g. "<="&TODAY()).
  5. Add another comma and then specify the range of dates again as the second criterion (e.g. A2:A6).
  6. Add another comma and type in ">"&TODAY()-7 to include data from 7 days ago (e.g. ">"&TODAY()-7).
  7. Close the formula with a closing parenthesis ")" and press Enter.

Your final formula should look like this:

=SUMIFS(B2:B6, A2:A6, "<="&TODAY(), A2:A6, ">"&TODAY()-7)

The result will be 1,000, which is the sum of the sales data for the last 7 days from today's date.

Excel SUMIFS Troubleshooting

SUMIFS is a useful formula in Excel that allows you to sum values between two dates or within a certain date range. However, if your formula is not working properly, there are a few things you can check to identify and solve the problem.

  1. Ensure that your dates are in the correct format and that you are summing numbers and not numbers stored as text. Using Excel functions, you can change text dates to real dates or convert text to numbers.
  2. Check that you used the correct criteria syntax when checking dates using SUMIFS. Dates should be inside quotation marks, while cell references and functions should be outside the quotes.
  3. It is also important to verify the formula's logic, ensuring that the start date is preceded by the correct operator and the end date is prefixed by the appropriate symbol. A small mistake in the formula's logic can cause hours of debugging time.
  4. Ensure all ranges used in the SUMIFS function are the same size. If the sum range and criteria ranges have different numbers of rows and columns, a #VALUE! Error will occur.

Using Data Analysis Toolpak in Excel

Microsoft Excel has a feature called Data Analysis Toolpak that can help you quickly calculate various statistical measures like median, standard deviation, variance, and ANOVA. 

To use this feature, you need to activate it by going:

  1. Go to the "File" menu, then "Options" and "Add-Ins." 
  2. Select the "Data Analysis Toolpak" from there and click "OK." 
    Select the
  3. This will add a button to the "Data" tab in Excel.

To use the Data Analysis Toolpak:

  1. Select "Data Analysis" and "Descriptive Statistics.
  2. Choose the column of numbers you want to analyze and check the box if your data has headers. 
  3. Choose where the summary statistics appear and click "OK." 
  4. The results will be generated in the location you specified.

Excel Pivot Tables

Excel Pivot Tables are a helpful way to summarize and analyze data differently. Pivot tables are like a summary of data that allows you to look at information from different angles.

Creating a pivot table is easy:

  1. First, select the data you want to analyze
  2. Go to the Insert tab and click on Pivot Table
    Go to the Insert tab and click on Pivot Table
  3. Excel will generate a table for you to use

From there, you can choose which columns you want to summarize. For example, you can see the total sales by department. You can also add more calculations like average, maximum, and minimum.

If you want to see the percentage of sales for each department, right-click on a number in the pivot table and choose "Show Value As > % of Grand Total." This will show you how much each department contributed to the total sales.

Go to the Insert tab and click on Pivot Table

 

You can also add more columns or rows to get a 3D view of your data.

Pivot tables are a great tool for summarizing data quickly and efficiently. You can use them to analyze data from single or multiple worksheets.

Using Slicers for Excel Data Analysis

With Slicers, you can quickly create a slice of your data based on your chosen columns. Here are the steps to use Slicers:

  1. Go to Table Design
  2. Select Insert Slicer
  3. Pick the column you want to filter on
    Pick the column you want to filter on
  4. Click on any value in the Slicer to filter the data
  5. You can make multiple selections by holding the control key
  6. You can add more than one Slicer for different combinations of slices
  7. The total row updates automatically as each selection is made
    The total row updates automatically as each selection is made

Using only your mouse is a simple and easy way to analyze and summarize data in Excel spreadsheets. You don't need any formulas or complicated steps. 

Slicers help you to quickly understand your data by looking at it from different angles.

FAQs

How do I summarize specific cells in Excel?

To summarize specific cells in Excel, you can use functions like SUM, AVERAGE, COUNT, or other aggregate functions to perform calculations on selected cells based on specific criteria or ranges.

Can I do a subtotal with Sumif?

No, the SUMIF function is used to add the values in a range that meet a specific condition, but it does not provide a direct way to calculate subtotals. However, you can achieve subtotals by combining the SUMIF function with other functions or using the Subtotal feature in Excel.

Why do we use Sumif formula in Excel?

The SUMIF formula in Excel is used to add values in a range that meet specific criteria, allowing you to selectively summarize data based on conditions or criteria that you define.

What is the difference between sum and Sumif?

The SUM function in Excel is used to add up a range of values, while the SUMIF function adds up values in a range that meet a specific condition or criteria that you specify.

How do I do a Sumif with multiple criteria in Excel?

To do a SUMIF with multiple criteria in Excel, you can use the SUMIFS function, which allows you to specify multiple conditions and sum the corresponding values that meet all of the specified criteria.

Final Thoughts

In conclusion, Excel provides various tools to summarize data quickly and efficiently. 

The SUMIF function is useful for conditionally sum values based on a single criterion. 

The Data Analysis ToolPak and Pivot Tables offer more advanced ways to summarize data, while Slicers allow for easy filtering and slicing of data. 

Exploring these tools to understand your data better and gain insights that can inform decision-making is essential. 

With practice and familiarity with these tools, users can become proficient in summarizing data in Excel.

One more thing

If you have a second, please share this article on your socials; someone else may benefit too. 

Subscribe to our newsletter and be the first to read our future articles, reviews, and blog post right in your email inbox. We also offer deals, promotions, and updates on our products and share them via email. You won’t miss one.

Related articles 

» How to Limit Rows and Columns in an Excel Worksheet
» Troubleshooting "OLE Action Excel" Error in Microsoft Excel
» Ways To Alternate Row Colors in Excel [Guide/]

Tags: How To