How To Sum Multiple Columns In Excel

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

In Excel, you may e'er 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 - January, Feb and Mar.

Sum multiple columns based on single criteria with a helper column

Sum multiple columns based on unmarried 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 cavalcade

In Excel, nosotros can create a helper cavalcade to sum the total values for each row, and so use the sumif function to sum this cavalcade based on criteria, delight do every bit this:

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

2. And next, y'all can use the beneath sumif function to sum the data in helper column E based on the criteria:

=SUMIF(A2:A10, G2, E2:E10)

Then, press Enter key on the keyboard, and yous volition go 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 utilise the criteria against;
G2 is the criterion that the items are to be added;
E2:E10 specifies the cells to be added.

lookup and sum all the matching values in rows or columns

Kutools for Excel'south LOOKUP and Sum characteristic helps y'all to lookup the specific value and become the summation of all the matching values in rows or columns as you need. Click to download Kutools for Excel!

Kutools for Excel: with more than 300 handy Excel add together-ins, gratis to attempt with no limitation in 30 days. Download and gratis trial Now!

Sum multiple columns based on single criteria with an array formula

If you don't like to use the helper column to solve this trouble, here besides is an array formula that can practice you a favor.

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

=SUM((B2:B10+C2:C10+D2:D10)*(--(A2:A10=F2)))

2. And and then press the Ctrl + Shift +Enter keys together, and yous volition become the right result.

Tips: In the to a higher place formula：

B2:B10 , C2:C10 and D2:D10 , signal the columns that y'all desire to sum, if yous have more columns data need to sum, simply add the column range every bit your need;
A2:A10 refers to the range of cells that yous want to utilise 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 every bit quickly as possible, Kutools for Excel's LOOKUP and Sum feature besides can help yous.

Tips: To apply this LOOKUP from Bottom to Superlative characteristic, firstly, you should download the Kutools for Excel, and so apply the feature quickly and easily.

After installing Kutools for Excel, please do as this:

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

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

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

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

Download and free trial Kutools for Excel Now!

The All-time Function Productivity Tools

Kutools for Excel Solves Almost of Your Problems, and Increases Your Productivity by eighty%

Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with countersign; Create Mailing List and send emails...
Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit big numbers of cells); Paste to Filtered Range...
Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Forestall Duplicate Cells; Compare Ranges...
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Discover and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Excerpt Text, Add Text, Remove by Position, Remove Infinite; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Information into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More 300 powerful features. Supports Role/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Total features xxx-day free trial. 60-twenty-four hours money back guarantee.

kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

Enable tabbed editing and reading in Word, Excel, PowerPoint , Publisher, Access, Visio and Project.
Open up and create multiple documents in new tabs of the same window, rather than in new windows.
Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every twenty-four hours!

officetab bottom