SUMIFS Multiple Columns in Excel (5 Types of Application)

In certain circumstances, we have to calculate the sum value dealing with multiple columns. The SUMIFS function is a good choice in those cases.

In this article, I’ll demonstrate five types of application of SUMIFS multiple columns in Excel. Also, I’ll show an effective alternative to the function.

Download Practice Workbook

Basics

This section is mainly for beginners. Here, we’ll understand the SUMIFS function and the process of entering the formula in Excel. If you think, you’ve got these basics, move to the main section.

What is SUMIFS Function?

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

The syntax of the function is

=SUMIFS (sum_range, range1, criteria1, [range2], [criteria2], ...)

There are the following arguments in the function.

sum_range – The range to be summed.

range1 – The first range to evaluate.

criteria1 – The criteria to use on range1.

range2 – [optional] The second range to evaluate.

How to Enter a Formula in Excel

Do you know how can we insert a formula in Excel?

Entering a formula in the Excel formula bar is quite a simple task.

First, you have to select a blank cell where you want to show the output. Then input an equal sign (=). And then insert the formula with proper parenthesis. At last, press Enter. Furthermore, you can use the Fill Handle Tool (appears in the lower right of the selected cell as a small square) to copy the formula for other cells.

SUMIFS Multiple Columns in Excel

Let’s have a look at our 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.

Dataset of SUMIFS Multiple Columns

Right now, we’ll do analysis based on SUMIFS multiple columns.

Let’s get started.

1. SUMIFS Multiple Columns with Single Criteria

If you want to use single criteria e.g. the total number of visits to every site based on multiple columns and application of SUMIFS function, you may proceed with the following formula.

=SUMIFS($E$5:$E$15,$B$5:$B$15,G5)

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.

SUMIFS Multiple Columns with Single Criteria

2. SUMIFS Multiple Columns with Multiple Criteria

What will happen if you want to use multiple criteria for multiple columns?

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

As a case in our dataset, you can calculate the total number of visits based on the name of sites and the platform.

In that case, the formula takes the following form

=SUMIFS($E$5:$E$15,$B$5:$B$15,G5,$C$5:$C$15,H5)

Here, E5:E15 is the cell range for the number of visits, 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 a name of platform namely web.

SUMIFS Multiple Columns with Multiple Criteria

3. SUMIFS Multiple Columns Using OR Logic

Let’s imagine another situation where you wish to calculate the total number of visits for every site based on a month like June.

You may follow the formula as shown below. Don’t get afraid while watching the formula. Truly speaking, it’s easier to understand and actually the combination of three SUMIFS functions (for three sites).

=SUMIFS(E5:E15,B5:B15,H6,D5: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")

Remember, 6/1/2021 and 6/30/2021 for the whole month of June. Now, let’s understand the formula in part by part.

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.

In the Second SUMIFS Syntax (for the site softeko.digital),

E5:E15 is the cell range for the number of visits, B5:B15 is for the name of sites, H7 is the name of the site, and D5:D15 is for the dates.

In the Last SUMIFS Syntax (for the site udemy.com)

E5:E15 is the cell range for the number of visits, B5:B15 is for the name of sites, H8 is the name of the site, and D5:D15 is for the dates.

After entering the whole formula, you’ll get 18055 as the total number of visits.

SUMIFS Multiple Columns Using OR Logic

4. SUMIFS Multiple Columns with AND Logic

Again, you may calculate the total considering specific criteria e.g. you want to count if the number of visits is greater than 2500 for every site.

In that situation using SUMIFS function with AND logic, the formula will be

=SUMIFS(E5:E15,B5:B15,G5,E5:E15,">=2500")

In the formula, E5:E15 is the cell range for the number of visits, B5:B15 is for the name of sites, and >=2500 is for if the number of visits is greater than 2500.

SUMIFS Multiple Columns with AND Logic

5. SUMIFS Multiple Columns with Dates (Date Range)

Also, you may find the sum for the number of visits using SUMIFS multiple columns for a specific date range. Like you need the total visits from 25-Jun-2021(start date) to 3-June-2021(end-date) for a particular site.

So, the formula will be

=SUMIFS(E5:E15,B5:B15,H6,D5:D15, ">="&H5, D5:D15, "<="&I5)

Here, 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, D5:D15 is for the dates, H5 is the start date, and I5 is the end date.

SUMIFS Multiple Columns with Dates

Alternative of Using SUMIFS

SUMPRODUCT Multiple Columns with Multiple Criteria

SUMPRODUCT is an extraordinarily multifaceted, but rather flexible function that is suitable for summing such as SUMIFS.

The syntax of SUMPRODUCT function is

=SUMPRODUCT (array1, [array2], ...)

There are the following arguments in the function.

array1 – The first array or range to multiply, then add.

array2 – [optional] The second array or range to multiply, then add.

In this article, I am showing the use of the SUMPRODUCT function in case of multiple criteria that we did similar to our second method.

We are going to find the total number of visits based on the name of the sites and the platform.

The formula will be

=SUMPRODUCT(($B$5:$B$15=G5)*($C$5:$C$15=H5)*($E$5:$E$15))

Here, 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 a name of platform namely web, and E5:E15 is the cell range for the number of visits.

SUMPRODUCT Multiple Columns with Multiple Criteria

Things To Keep in Mind

  • Don’t forget to use the 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

This is how we can apply the SUMIFS function for multiple columns in different aspects. Also, I have shown an effective alternative to the function. Choose one according to your requirement.

Thanks for visiting exceldemy.com, a valuable source of Excel problems-solutions.

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