SUMIFS with Multiple Criteria Along Column and Row in Excel

Introduction to SUMIFS Function in Excel

The SUMIFS function is a math and trig function. It adds all of its arguments that meet multiple criteria.

• Function Objective:

Add the cells given by specified conditions or criteria.

• Syntax:

• Arguments Explanation:

• Return Value:

The sum of the cells in a numeric value that meets all given criteria.

• Available in Version:

Office 365  ￭ Excel 2019 ￭ Excel 2016  ￭  Excel 2013  ￭ Excel 2011 for Mac ￭  Excel 2010 ￭ Excel 2007.

Our sample dataset that contains the product, customer, date, and sales of a Fruit Shop.

Method 1 – SUMIFS with Comparison Operators and Multiple Criteria Along Two Columns

From our data set, we want to know the sum of sales to John that are less than 22 dollars.

• Go to Cell D17.
• Enter the SUMIFS function.
• In the 1st argument select the range E5:E13.
• In the 2nd argument select the range C5:C13 and select Cell D15 as the 1st criterion for John.
• Add 2nd criteria of range E5:E13 that contains the price. Then select smaller than sign and Cell D16. The formula becomes:

• Press Enter.
• The outcome is the total sales to John less than 22 dollars each.

Read More: How to Apply SUMIFS with Multiple Criteria in Different Columns

Method 2 – Use SUMIFS in Excel with Date Criteria in Column

Let’s calculate the sales for the last 30 days. We will count today to the last 30 days.

Steps:

• Set the start and end dates.
• Go to cell D17.
• Enter the SUMIFS.
• In the 1st argument select the range E5:E13, which indicates the price.
• In the 2nd argument select the range D5:D13 that contains the date and input greater than equal sign and select cell D15 as the starting date.
• Add other criteria that are less than equal in the same range and select cell D16 as the ending date. The formula becomes:

• Press Enter.
• This is the sales amount for the last 30 days.
• You can do this for any specific date or date range.

Read More: Excel SUMIFS with Multiple Vertical and Horizontal Criteria

Method 3 – Excel SUMIFS with Blank Rows Criteria

Steps:

• Go to cell D16.
• Enter the SUMIFS.
• In the 1st argument select the range E5:E13, which indicates price.
• In the 2nd argument select the range B5:B13 and check blank cells.
• Add other criteria and that range is C5:C13. If both the columns alongside cells are blank, it will show an output. The formula becomes:

• Press Enter.
• We will see that 2 cells of each column are blank. And the output is the sum of them.

Method 4 – SUMIFS with Non-Blank Cells Criteria Along Column & Row

4.1 Using SUM-SUMIFS Combination

Steps:

• Add 3 cells in the data sheet to find our desired outcome.
• Get the total sales of Column E in cell D16.
• Enter the SUM function and the formula will be:

• Press Enter.

• In the D17 cell enter the formula of sales of blank cells that we showed in the previous method. The formula will look like this:

• Press the Enter button.

• Subtract these blank cells from the total sales in the D18 cell. The formula will be:

• Press Enter.
• The output will be the total sales of non-blank cells.

4.2 Using SUMIFS Function Alone

Steps:

• Go to Cell D16
• Enter the SUMIFS
• In the 1st argument select the range E5:E13, which indicates the price.
• In the 2nd argument select the range B5:B13 and check blank cells.
• Add other criteria and that range is C5:C13. If both the column’s same cell is non-blank, it will show an output that is the sum. The formula becomes:

• Press Enter.
• The output of all the non-blank cells is shown.

Method 5 – SUMIFS + SUMIFS for Multiple OR Criteria Along Column and Row

In this method, we want to apply multiple criteria multiple times. We used SUMIFS two times here to complete the task.

• Go to Cell D19.
• Enter the SUMIFS function.
• In the 1st argument select the range E5:E13, which indicates price.
• In the 2nd argument, select the range C5:C13 and select D17 as the reference customer.
• Add the date criteria. The formula becomes:

• Press Enter.
• The output is the sum of John and Alex in each period.

Read More: How to Use SUMIFS with Multiple Criteria in the Same Column

Excel SUMPRODUCT Function: Alternative to SUMIFS for Matching Multiple Criteria Along Column and Row

There are some alternative options through which we can achieve the same outputs.

Alternative 1 – SUMPRODUCT with Multiple AND Criteria

We will apply the SUMPRODUCT function for AND type multiple criteria along with columns and rows. For instance, we will calculate the total products sold to John after 1st October which is higher than 15 dollars. SUMPRODUCT function will be used to make the solution easier.

Steps:

• Go to cell D18.
• Enter SUMPRODUCT  and select range E5:E13 in the 1st argument which indicates the price. The formula becomes:

• Press Enter to get the result.

Read More: SUMIFS: Sum Range Across Multiple Columns

Alternative 2 – SUMPRODUCT with Multiple OR Criteria

Steps:

• Go to cell D18
• Enter SUMPRODUCT and select range. The formula becomes:

• Press the Enter button.

Download Practice Workbook

Related Articles

• How to Apply SUMIFS with INDEX MATCH for Multiple Columns and Rows
• Exclude Multiple Criteria in Same Column with SUMIFS Function
• How to Use VBA SUMIFS with Multiple Criteria in Same Column

<< Go Back to Excel SUMIFS with Multiple Criteria | Excel SUMIFS Function | Excel Functions | Learn Excel