Excel SUMIFS with Multiple Columns (8 Applications)

If you want to sum the values of some cells when they meet one or more criteria, then the SUMIFS function is an excellent choice. There are plenty of uses for the function in Excel. You can use it with many other functions also. In this article, I will show you 8 effective applications of the Excel SUMIFS function in multiple columns.


Download Practice Workbook

You can download the practice workbook from here for practicing.


Introduction to Excel SUMIFS Function

SUMIFS function is an Excel function that adds all values based on multiple criteria. This function was introduced in 2007. Since its initiation, it’s become popular day by day.

  • Syntax

The syntax of the function is

=SUMIFS(sum_range, range1, criteria 1, [range2]), [criteria2],…)

  • Arguments

There are the following arguments in the function.

sum_range – The range to sum.

range1 – The first range as criteria.

criteria1 The criteria for range1.

range2 – [optional] The second range as criteria.

criteria2 The criteria for range2.

We can add more criteria ranges and criteria as arguments.


8 Effective Applications of SUMIFS Function with Multiple Columns in Excel

The SUMIFS function can be used in various ways. Here, I will show 8 effective applications of the SUMIFS function in multiple columns.

1. Use SUMIFS in Multiple Columns with Single Criteria

The SUMIFS function can be used to sum values that meet a single criterion. I will explain the procedure to do that with the following example. In the dataset, Some popular Name of Sites is given along with the Number of Visits. Besides, the Platform which is used and also the Date of counting the number of visits are provided. Let’s follow the steps given below to sum the number of visits for each site.

  • First, select cell H5.

SUMIFS Multiple Columns

  • Next, write the following formula in the cell and hit Enter.
=SUMIFS($E$5:$E$15,$B$5:$B$15,G5)
  • Further, use the Fill Handle to copy the formula to the cells below.
  • Hurrah! We have calculated the Total Visits for each site in the dataset.

Here, E5:E15 is the cell range for the number of visits, B5:B15 is for the name of sites, and G5 is the cell of a site namely exceldemy.com.

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


2. Apply SUMIFS with Multiple Criteria in Multiple Columns

Now, I will show the application of the SUMIFS function with multiple criteria in multiple columns. I will use the dataset of Application 1  for demonstrating the procedure. Let’s follow the given steps.

  • Firstly, select cell I5 and write the following formula there.
=SUMIFS($E$5:$E$15,$B$5:$B$15,G5,$C$5:$C$15,H5)
  • After that, hit Enter.
  • Further, use the AutoFill option to copy the formula in the cells below.

Apply SUMIFS with Multiple Criteria in Multiple Columns

Here, E5:E15 (sum_range) is the cell range for the number of visits, B5:B15 (criteria range 1) represents the names of sites, G5 (criteria1) is the cell of a site exceldemy.com, C5:C15 (criteria range2) is for the platform, and H5 (criteria2) is the name of the platform web.

Read More: SUMIFS Multiple Criteria Different Columns (6 Effective Ways)


3. Insert Excel SUMIFS in Multiple Columns with OR Logic

The SUMIFS function can be used with OR logic to add the values which meet multiple criteria. In the dataset, there is the Name of Sites, and Number of Visits with their respective Date. Suppose we want the total number of visits for three sites for the month of June. Let’s follow the steps given below.

  • Firstly, select cell H9 and write the following formula there.
=SUMIFS(E5:E15,B5:B15,H6,D5:'SUMIFS_OR Logic'!D15,">=6/1/2021",D5:D15,"<=6/30/2021")+SUMIFS(E5:E15,B5:B15,H7,D5:D15,">=6/1/2021",D5:D15,"<=6/30/2021")+SUMIFS(E5:E15,B5:B15,H8,D5:D15,">=6/1/2021",D5:D15,"<=6/30/2021")
  • Then, press Enter.
  • Bravo! we will see the total number of visits from the sites for the month of June.

Insert Excel SUMIFS in Multiple Columns with OR Logic

Note: Remember, 6/1/2021 and 6/30/2021 mean the whole month of June.

🔎 How Does the Formula Work?

Now, let’s understand the formula.

  • In the first SUMIFS Syntax (for the site exceldemy.com), E5:E15 is the cell range for the number of visits, B5:B15 is for the name of sites, H6 is the name of the site, and D5:D15 is for the dates.
  • Similarly, two more SUMIFS functions are used and their results are added by using OR logic(+).

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


Similar Readings


4. Use AND Logic with Excel SUMIFS Function

The SUMIFS function can also be used with the AND operator. For demonstrating the procedure I have used the dataset from Application 1. Here, I will calculate the sum of the Number of Visits for exceldemy.com if the values are greater than 2500. I am showing the steps for this application below.

  • First, select cell I5 and write the following formula.
=SUMIFS(E5:E15,B5:B15,G5,E5:E15,">=2500")
  • Next, press Enter.
  • We will see the Total Visits for exceldemy.com if the number of visits for a day is more than or equal to 2500.

In the formula, E5:E15 is the cell range for the number of visits, B5:B15 is for the name of sites, and G5 is the criteria. Again, E5:E15 is the criteria range and >=2500 is the 2nd criterion.

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


5. Insert SUMIFS in Multiple Columns Having Dates (Date Range)

We can use the SUMIFS function to sum values between two dates. I have used the dataset from Application 1 to explain the use of the SUMIFS in multiple columns having dates. Let’s follow the steps below for that.

  • Firstly, select cell H7 and write the following formula there.
=SUMIFS(E5:E15,B5:B15,H6,D5:D15, ">="&H5, D5:D15, "<="&I5)
  • Then, hit Enter.
  • Consequently, we will see the result.

Insert SUMIFS in Multiple Columns Having Dates (Date Range)

Here, E5:E15 (sum range) is the cell range for the number of visits, B5:B15 (criteria range1) is for the name of sites, H6 (criteria1) is the name of the site, D5:D15 (criteria range2) is for the dates, H5 (criteria2) is the start date. Again, D5:D15 criteria range3)   and I5 (criteria3) is the end date.

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


6. Apply SUMIFS Function for Empty Cells in Excel

The SUMIFS function can also sum values taking empty cells as criteria. In the dataset, I have taken some Fruit names, their Order Date and Delivery Date, and the delivered Quantity. Follow the steps given below to sum the Quantity if it was not delivered (Delivery Date is empty). Let’s follow the steps given below.

  • Select cell B14 and write the formula given below.
=SUMIFS(E5:E11,C5:C11,"<> ",D5:D11,"=")
  • Next, press Enter.
  • Consequently, we will see the Quantity of Not Delivered Products.

In the formula,

  • E5:E11 is the sum range
  • C5:C11 is the range of Order Date, and “<> “ is the criteria for this range which means not equal to Blank.
  • The range of Delivery Date is D5:D11 and “=” is the criteria for this range which means equal to Blank. ( You can use ” ” instead of “=” also)

7. Combine SUMIFS and SUM Functions in Multiple Columns

We can use the SUMIFS function and the SUM function together to sum values from multiple columns. For explaining this application I introduced State, Product, and Sales. We will calculate Total Sales if the criteria match. Let’s follow the steps given below for this application.

  • First, select cell G8.
  • Then, write the following formula there and hit Enter.
=SUM(SUMIFS(D5:D14,C5:C14,G5,B5:B14,{"Texas","Florida"}))
  • Finally, we will see the Total Sales for values that match the criteria.

Combine SUMIFS and SUM Functions in Multiple Columns

🔎 How Does the Formula Work?

  • In the formula, here in the SUMIFS function, an array was selected as criteria. This array contains two different values. The function will look for both of these values separately and return the sum for both.

Output: {1300,2200}

  • SUM(SUMIFS(D5:D14,C5:C14,G5,B5:B14,{“Texas”,”Florida”})) turns into SUM({1300,2200}).
  • Here, the SUM function will return the summation of these 2 values.

Output: 3500


8. Use Wildcard Characters in SUMIFS Function with Multiple Criteria

I will show you how you can use Wildcard Character (~,*,?) in the SUMIFS function for multiple criteria in Excel. For this example, I will use the Asterisk (*) symbol. Suppose we have a dataset containing Sales Person, Product, and Sales column. And we want to calculate the Total Sales of Mobile from Sales Person whose names contain the letter “n”. let’s follow the steps given below for this application of the SUMIFS function.

  • First, select cell G8 and write the following formula there.
=SUMIFS(D5:D14,C5:C14,G5,B5:B14,"*n*")
  • Then, hit Enter.
  • Consequently, we will see the Total Sales for Sales Person whose names have the letter (n).

Here, in the SUMIFS function, I selected cell range D5:D14 as sum_range, C5:C14 as criteria_range1, G5 as criteria1, B5:B14 as criteria_range2, and “*n*” as criteria2. Here, “*n*” means any word that contains the letter “n”. Now, the formula will sum the values from the cell range D5:D14 that meet both criteria1 and criteria2.

Alternative of the Uses of SUMIFS with Multiple Columns in Excel

Instead of the SUMIFS function, we can use the SUMPRODUCT function to sum values that match multiple criteria. We have used the dataset from Application 1 to demonstrate this application. Follow the steps given below for this alternative application.

  • First, select cell I5 and write the following formula there.
=SUMPRODUCT(($B$5:$B$15=G5)*($C$5:$C$15=H5)*($E$5:$E$15))
  • Afterward, press Enter.
  • Instantly, we will see the result which is the sum of values that match the criteria.

Alternative of Using SUMIFS Function in Multiple Columns in Excel

In the formula, B5:B15 is for the name of sites, G5 is the cell of a site namely exceldemy.com, C5:C15 is for the platform, and H5 is the name of a platform (web), and E5:E15 is the cell range for the number of visits.

Things to Remember

  • Don’t forget to use double quotes (e.g. input as “<“).
  • Input the formula with correct logic (e.g. don’t input “>” instead of “>=”).
  • Be careful about the file name, file location, and Excel extension name.

Conclusion

The SUMIFS function is widely used to sum values that match certain criteria. In this article, I have shown 8 effective applications of the SUMIFS function. Hopefully, it will help you to understand the use of the SUMIFS function in various ways. If you have any queries or suggestions, please leave a comment in the comment section.


Related Articles

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo