Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use SUMIF Function Across Multiple Columns in Excel

While working with Microsoft Excel, summing up multiple columns is a very common scenario. To add cells with conditions, we use the SUMIF function. We usually use this function to add based on one column. But, we can also use this for multiple columns. In this tutorial, we are going to show you how to use the SUMIF function across multiple columns in Excel.

Overview Image


Download Practice Workbook

Please download the following workbook to practice along with it.


4 Methods to Use the SUMIF Function across Multiple Columns

In the next sections, we will show how you can use the SUMIF function for multiple columns. We are going to demonstrate four methods. We highly recommend you learn and try all these methods for your dataset. Undoubtedly, it will solve your problem.

The Basic Syntax of the SUMIF Function:

=SUMIF(range,criteria,Sum_range)

To demonstrate this tutorial, we are going to use the following dataset:

dataset for SUMIF across multiple columns

From the dataset, we are going to add values based on multiple columns. We want to know the total sales of the salesperson John across all the months of January, February, and March for all products.


1. Combining Multiple SUMIF Functions for Multiple Columns

The easiest way to use the SUMIF function across multiple columns is to combine multiple SUMIF functions.

The Generic Formula:

=SUMIF(range,criteria,sum_range)+SUMIF(range,criteria,sum_range)+……..
 Now, to calculate the total sales of all salespersons for March, follow the steps below.

📌 Steps:

  • Type the following formula in cell C15:
=SUMIF(C5:C12,B15,D5:D12)+SUMIF(C5:C12,B15,E5:E12)+SUMIF(C5:C12,B15,F5:F12)

Combining Multiple SUMIF Functions for Multiple Columns in Excel

  • Then, press ENTER.

Final Image of SUMIF across multiple columns in Excel

As you can see, we have successfully added up the total sales of John across all the months.


Read More: SUMIF with Multiple Criteria for Different Columns in Excel


2. Applying SUMIF and SUMPRODUCT Functions in Multiple Columns

By using the SUMIF and SUMPRODUCT functions, we can also sum multiple salesperson‘s sales.

Now, to calculate the total sales of all salespersons for March, follow the steps below.

📌 Steps:

  • First, type the following formula in cell D15:
=SUMPRODUCT(SUMIF(C5:C12,C15:C17,F5:F12))
  • Then, press ENTER.

Using SUMPRODUCT and SUMIF Functions across multiple columns

Finally, you can see we have successfully counted the total sales of all the salespersons for March.


Read More: Sum Multiple Columns Based on Multiple Criteria in Excel


3. Utilizing SUMIF and SUM Functions Across Multiple Columns

Now, by combining the SUM and SUMIF functions we can find total sales across multiple columns.

The Generic Formula:

=SUM(SUMIF(range,criteria2,sum_range1),SUMIF(range,criteria2,sum_range2)…….)

In this method, we want to find the total sales of the product Book in February and the total sales of the product Pen in January. Let’s see the following steps.

📌 Steps:

  • First, type the following formula in cell C15:
=SUM(SUMIF(B5:B12,B15,E5:E12),SUMIF(B5:B12,B16,D5:D12))

Utilizing SUMIF and SUM Functions Across Multiple Columns in Excel

  • Then, press ENTER.

Comparison between multiple columns using SUMPRODUCT function

Finally, you can see we are successful in finding the total sales of the product Book in February and the total sales of the product Pen in January.


Read More: SUMIF Multiple Ranges [6 Useful Ways]


Similar Readings


4. Employing SUMIF Function Across Multiple Columns with Helper Column

Now, this formula is a little bit tricky. We are not directly using the SUMIF function across multiple columns. We are creating a new column and adding a subtotal of another column. Then, we are using the SUMIF function on that column.

To find the total sales of the product Eraser across all the months, let’s follow the steps below.

📌 Steps:

  • First, create a new column “Sub-total”.

Helper Column for SUMIF across multiple columns

  • Secondly, type the following formula in cell G5:
=SUM(D5:F5)
  • Then, press ENTER.
  • Eventually, Drag the Fill Handle icon over the range of cells G6:G12 to get the following output.

SUM function

  • Now, in cell C15, type the following formula:
=SUMIF(B5:B12,B15,G5:G12)
  • Then, press ENTER.

Employing SUMIF Function Across Multiple Columns with Helper Column

Finally, you can see we have calculated the total sales of the product Eraser across all the months.


Practice Section

We have provided a Practice Section on each sheet on the right side for your practice. Please do it by yourself.

Practice Section


Conclusion

To Conclude, I hope these methods will help you to use the SUMIF function across multiple columns in Excel. Moreover, download the practice workbook and try these yourself. Also, feel free to give your feedback in the comment section. Your valuable feedback keeps us motivated and helps us to create articles like this. And don’t forget to check our website ExcelDemy for various Excel-related articles.


Related Articles

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo