SUMIFS with Multiple Criteria Along Column and Row in Excel

Excel is part and parcel of our daily life. When we think about data, the first thing that comes to our mind is Excel. We can do all sorts of data manipulation with Excel. Working with large amounts of data, we need to use the SUMIFS function. In this article, we will discuss the use of SUMIFS with multiple criteria along column and row in Excel.

We take a data set that contains the product, customer, date, and sales of a Fruit Shop.

Data set for SUMIFS multiple criteria along column and row


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Introduction to SUMIFS Function

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

  • Syntax
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
  • Argument

sum_range – Data of this range will be summed.

criteria_range1 – This range will be used for  Criteria1.

criteria1 – This is the condition that will be applied to the cells of criteria_range1.


5 Methods to Use SUMIFS Function in Excel with Multiple Criteria Along Column and Row

Here, we will apply 5 different methods using SUMIFS. Before that add criteria and outcome cells in the data set.


1. Excel 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.

Here, the 1st criteria are the amount sold to John and 2nd one is the price is less than 22 dollars. Now, set these two criteria in the sheet. Inputs are John in the Customer box and 22 in the Price box.

Step 1:

  • Go to Cell D17.
  • Write down the SUMIFS function.
  • In the 1st argument select the range E5:E13, which value we want.
  • In the 2nd argument select the range C5:C13 and select Cell D15 as the 1st criteria for John.
  • Add 2nd criteria of range E5:E13 that contains the price. Then select smaller than sign and Cell D16. The formula becomes:
=SUMIFS(E5:E13,C5:C13,D15,E5:E13,"<"&D16)

Excel SUMIFS with Comparison Operators and Multiple Criteria Along Column & Row

Step 2:

  • Press Enter.

Excel SUMIFS with Comparison Operators and Multiple Criteria Along Column & Row

This outcome is the total sales to John less than 22 dollars each.

Read more: SUMIFS Multiple Criteria Different Columns


2. Use of SUMIFS with Date Criteria in Column

Here we will find sales compared to date. Let’s calculate the sales for the last 30 days. We will count today to the last 30 days.

Step 1:

  • First, we will set the start and end dates.
  • We will use the Today () function to set the dates. It returns the present day’s date.
  • In the start date, subtract 30 from Today ().

SUMIFS with Date Criteria in Column

Step 2:

  • Go to Cell D17.
  • Write down 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. So, the formula becomes:
=SUMIFS(E5:E13,C5:C13,D15,E5:E13,"<"&D16)

SUMIFS with Date Criteria in Column

Step 3:

  • Then, press Enter.

This is the sales amount of the last 30 days. We can do this for any specific date or date range.

Read More: How to Use SUMIFS with Date Range and Multiple Criteria (7 Quick Ways)


3. Excel SUMIFS with Blank Rows Criteria

We can make a report of blank cells by the SUMIFS function. For this, we need to modify our data set. Remove elements from the Product and Customer column, so that we can apply the function with some criteria. So, the data set will look like this.

Excel SUMIFS with Blank Row

Step 1:

  • Go to Cell D16.
  • Write down 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 then it will show an output. So, the formula becomes:
=SUMIFS(E5:E13,B5:B13, "",C5:C13, "")

Excel SUMIFS with Blank Row

Step 2:

  • Now, press Enter.

SUMIFS with Blank Cells

Here, we will see that 2 cells of each column are blank. And the outcome is the sum of them.

Read More: [Fixed]: SUMIFS Not Working with Multiple Criteria (3 Solutions)


Similar Readings


4. Excel SUMIFS with Non-Blank Cells Criteria Along Column & Row

We can get by in two ways. Using the SUM with SUMIFS function and only the SUMIFS function.

4.1 Using SUM-SUMIFS Combination

We can easily get this with the help of method 3.

Step 1:

  • First, add 3 cells in the datasheet to find our desired outcome.

Excel SUMIFS with Non-Blank Cells Criteria along Column & Row

Step 2:

  • First, get the total sales of Column E in cell D16.
  • Write the SUM function and the formula will be:
=SUM(E5:E13)

Excel SUMIFS with Non-Blank Cells Criteria along Column & Row

Step 3:

  • Now, press Enter.

Excel SUMIFS with Non-Blank Cells Criteria along Column & Row

Step 4:

  • In the D17 cell write down the formula of sales of blank cells that we get in the previous method. And the formula will look like this:
=SUMIFS(E5:E13,B5:B13,"",C5:C13,"")

Excel SUMIFS with Non-Blank Cells Criteria along Column & Row

Step 5:

  • Again, press the Enter button.

Step 6:

  • Now, subtract these blank cells from the total sales in the D18 cell. So, the formula will be:
=SUM(E5:E13)-SUMIFS(E5:E13,B5:B13,"",C5:C13,"")

Excel SUMIFS with Non-Blank Cells Criteria along Column & Row

Step 7:

  • Finally, press Enter.

The outcome is the total sales of non-blank cells.


4.2 Using SUMIFS Function

We can also get the total of all non-blank cells using the SUMIFS function only.

Step 1:

  • Go to Cell D16
  • Write down 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 column’s same cell is non-blank, then it will show an output that is the sum. So, the formula becomes:
=SUMIFS(E5:E13, B5:B13, "<>",C5:C13, "<>")

Excel SUMIFS with Non-Blank Cells Criteria along Column & Row

Step 2:

  • Again, press Enter.

Excel SUMIFS with Non-Blank Cells Criteria along Column & Row

This is the output of all the non-blank cells.

Note:

<> – It means not equal.

Read More: Excel SUMIFS Not Equal to Multiple Criteria (4 Examples)


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.

First, we take John as a reference. We want to find out the total sales to John between 1st October to 15th October. In the 2nd criterion take Alex as a reference with the same period. So, the data set will look like this:

SUMIFS + SUMIFS for Multiple OR Criteria along Column and Row

Step 1:

  • Go to Cell D19.
  • Write down 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.
  • Then add the date criteria. So, the formula becomes:
=SUMIFS(E5:E13,C5:C13,D17,D5:D13,">="&D15,D5:D13,"<="&D16)+SUMIFS(E5:E13,C5:C13,D18,D5:D13,">="&D15,D5:D13,"<="&D16)

SUMIFS + SUMIFS for Multiple OR Criteria along Column and Row

Step 2:

  • Again, press Enter.

This is the sum of John and Alex in each period.

Read more:  SUMIF with Multiple Criteria in Column & Row in Excel (Both OR and AND Type)


Alternatives to SUMIFS Function for Matching Multiple Criteria Along Columns and Rows in Excel

There are some alternative options through which we can achieve the same outputs. And in fact, they are a bit easier sometimes.

1. SUMPRODUCT with Multiple AND Criteria

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

SUMPRODUCT Function with Multiple Criteria along Column and Row

Step 1:

  • Go to Cell D18.
  • Write down the SUMPRODUCT Select range E5:E13 in the 1st argument which indicates the price. And the formula becomes:
=SUMPRODUCT(E5:E13,(--(((D5:D13)>D15)*((C5:C13)=D16)*((E5:E13)>D17))))

SUMPRODUCT Function with Multiple Criteria along Column and Row

Step 2:

  • Now, press Enter.

See, we have got the result with less complication and using multiple criteria.

Read More: How to Apply SUMIFS with INDEX MATCH for Multiple Columns and Rows


2. SUMPRODUCT with Multiple OR Criteria

Here, we will apply the SUMPRODUCT function for OR type multiple criteria column and row. We want to calculate the total sales of Apple and Alex. It will include Apple and Alex both. We will do this by applying the SUMPRODUCT function. So, after modifying the data set will look like this:

SUMPRODUCT Function with Multiple Criteria along Column and Row

Step 1:

  • Go to Cell D18
  • Write down the SUMPRODUCT And the formula becomes:
=SUMPRODUCT(E5:E13,(--((--((B5:B13)=D15))+(--((C5:C13)=D16))>0)))

SUMPRODUCT Function with Multiple Criteria along Column and Row

Step 2:

  • Now, press the Enter button.

So, OR type multiple criteria can easily be solved in this way.

Read More: How to Use SUMIFS Formula with Multiple Criteria in Excel (11 Ways)


Conclusion

In this article, we showed different ways to use the SUMIFS with multiple criteria along column and row. We also attached alternative methods compared to SUMIFS. Hope this helps you get the exact solution. Stay with us and provide your valuable suggestions.


Related Readings

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo