How to sum multiple columns based on single criteria in Excel?

In Excel, you may always need to sum multiple columns based on one criteria. For example, I have a range of data as following screenshot shown, now, I want to get the total values of KTE in three months - Jan, Feb and Mar.

Sum multiple columns based on single criteria with a helper column

Sum multiple columns based on single criteria with an array formula

Sum multiple columns based on single criteria with an awesome feature

Sum multiple columns based on single criteria with a helper column

In Excel, we can create a helper column to sum the total values for each row, and then use the sumif function to sum this column based on criteria, please do as this:

1. In this example, you can sum the total values for each row first, please type this formula: =sum(B2:D2), then drag the fill handle down to the cells that you want to apply this formula, and the total values of each row will be displayedsee screenshot:

2. And next, you can apply the below sumif function to sum the data in helper column E based on the criteria:

Then, press Enter key on the keyboard, and you will get the total number based on the specific criteria. See screenshot:

Tips: In the above formula：

• A2:A10 refers to the range of cells that you want to apply the criteria against;
• G2 is the criterion that the items are to be added;
• E2:E10 specifies the cells to be added.

Sum multiple columns based on single criteria with an array formula

If you don’t like to use the helper column to solve this problem, here also is an array formula that can do you a favor.

1. Enter or copy the following formula in your specific cell - G2:

2. And then press the Ctrl + Shift +Enter keys together, and you will get the correct result.

Tips: In the above formula：

• B2:B10, C2:C10 and D2:D10, indicate the columns that you want to sum, if you have more columns data need to sum, just add the column range as your need;
• A2:A10 refers to the range of cells that you want to apply the criteria against;
• F2 is the criterion that the items are to be added.

Sum multiple columns based on single criteria with an awesome feature

to deal with this task as quickly as possible, Kutools for Excel's LOOKUP and Sum feature also can help you.

After installing Kutools for Excel, please do as this:

1. Click Kutools > Super LOOKUP > LOOKUP and Sum, see screenshot:

2. In the LOOKUP and Sum dialog box, please do the following operations:

• Select Lookup and sum matched value(s) in row(s) option under the Lookup and Sum Type section;
• Specify the lookup value, output range and the data range that you want to use;
• Select Return the sum of all matched values option from the Options.

3. Then, click OK button, and the total value of all the matched records has been calculated at once, see screenshot:

Download and free trial Kutools for Excel Now!

More relative articles: