Use of the SUMIF Function across Multiple Columns in Excel (4 Methods)

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.


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. Combine 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

1. Type the following formula in Cell I5:

=SUMIF(C5:C12,H5,D5:D12)+SUMIF(C5:C12,H5,E5:E12)+SUMIF(C5:C12,H5,F5:F12)

Adding sumif functions across multiple columns

2. Then, press Enter.

Result of adding sumif functions

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. Combine the SUMIF and SUMPRODUCT Functions to Apply 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

1. First, type the following formula in Cell J5:

=SUMPRODUCT(SUMIF(C5:C12,I5:I7,F5:F12))

sumif and sumproduct functions across multiple columns

2. Then, press Enter.

result of sumproduct and sumif 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. Combining the SUMIF and SUM Functions to Apply 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 total sales of the product Pen in January. Let’s see the following steps.

📌 Steps

1. First, type the following formula in Cell J5:

=SUM(SUMIF(B5:B12,H5,E5:E12),SUMIF(B5:B12,H6,D5:D12))

sum and sumif functions together

2. Then, press Enter.

result of SUM and SUMIF functions across multiple columns

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

Read More: SUMIF Multiple Ranges [6 Useful Ways]


Similar Readings


4. Use of the SUMIF Function across Multiple Columns with a 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 subtotal of another column. Then, we are using the SUMIF function on that column.

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

📌 Steps

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

creating Sub-total column

2. Secondly, type the following formula in Cell G5:

=SUM(D5:F5)

sum function in the sub-total column

3. Then, press Enter.

result of sum function

4. Next, Drag the Fill handle icon over the range of cells G6:G12.

drag the fill handle icon to copy the sum function

5. Now, in Cell J5, type the following formula:

=SUMIF(B5:B12,I5,G5:G12)

sumif function across multiple columns

6. Then, press Enter.

result of using sub-total column

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


Conclusion

To Conclude, I hope these methods will help you to use the SUMIF function across multiple columns. 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.com 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