A.N.M. Mohaimen Shanto
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.
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:
To demonstrate this tutorial, we are going to use the following dataset:
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:
📌 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)
- Then, press ENTER.
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.
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:
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))
- Then, press ENTER.
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
- SUMIF Across Multiple Sheets in Excel (3 Methods)
- SUMIF with Multiple Criteria (5 Easiest Examples)
- Excel SUMIF Function for Multiple Criteria (3 Methods + Bonus)
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”.
- 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.
- Now, in cell C15, type the following formula:
=SUMIF(B5:B12,B15,G5:G12)- Then, press ENTER.
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.
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.
