Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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 left screenshot shown, now, I want to get the total values of KTE in three months - Jan, Feb and Mar.

doc-sum-columns-one-criteria-1

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 Kutools for Excel


Advanced Combine Rows:(Combine duplicate rows and sum / average corresponding values):
  • 1. Specify the key column that you want to combine other column based on;
  • 2. Choose one calculation for your combined data.
  • doc-sum-columns-one-criteria-7

    Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


    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 a 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), see screenshot:

    doc-sum-columns-one-criteria-2

    2. 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 displayed, see screenshot:

    doc-sum-columns-one-criteria-3

    3. And next, you can apply the sumif function to sum the data in helper column E based on the criteria, enter this formula: =SUMIF(A2:A10, G2, E2:E10), see screenshot:

    doc-sum-columns-one-criteria-4

    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 is an optional argument, it specifies the cells to be added.

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

    doc-sum-columns-one-criteria-5


    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 can do you a favor.

    1. Enter this formula in your specific cell - G2, see screenshot: =SUM((B2:B10+C2:C10+D2:D10)*(--(A2:A10=F2))).

    doc-sum-columns-one-criteria-6

    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.

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


    Sum multiple columns based on single criteria with Kutools for Excel

    If you have Kutools for Excel, with its Advanced Combined Rows feature, you can sum corresponding values based on a criteria.

    Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days. 

    After installing Kutools for Excel, please do as this:

    1. Enter this formula to ge the total of each row, =sum(B2:D2), and then drag the fill handle down to the cells, see screenshot:

    doc-sum-columns-one-criteria-8

    2. Then select the data range, and click Kutools > Content > Advanced Combine Rows, see screenshot:

    doc-sum-columns-one-criteria-9

    3. In the Advanced Combine Rows dialog box, click the column that you want to sum based on, and then click Primary Key, see screesnhot:

    doc-sum-columns-one-criteria-10

    4. Then click the total column which you want to sum the values, and then click Calculate > Sum, see screenshot:

    doc-sum-columns-one-criteria-11

    5. After finishing the settings, please click OK button, and all corresponding values are added up based on the criteria, see screenshot:

    doc-sum-columns-one-criteria-12

     Download and free trial Kutools for Excel Now !


    Demo: Sum multiple columns based on single criteria with Kutools for Excel

    Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


    Related articles:

    How to sumif with multiple criteria in one column?

    How to sumif with one or more criteria in Excel?


    Excel Productivity Tools

    Ribbon of Excel (with Kutools for Excel installed)

    300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd!

    Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 80% Excel problems.

    •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
    •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
    •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
    •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
    •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
    •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
    •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
    •  Change the way you work now, and give you a better life immediately!

    Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

    • Increases your productivity by 50% when viewing and editing multiple documents.
    • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
    • Open and create documents in new tabs of same window, rather than in new windows.
    • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
    Say something here...
    symbols left.
    You are guest ( Sign Up? )
    or post as a guest, but your post won't be published automatically.
    Loading comment... The comment will be refreshed after 00:00.
    • To post as a guest, your comment is unpublished.
      Lizeth · 3 months ago
      Gracias por su publicacion! Me ayudo mucho!!!
      esra tan
      simple! jeje Gracias!
    • To post as a guest, your comment is unpublished.
      Nicole · 4 months ago
      Hi

      I have used the formula outlined in "Sum multiple columns based on single criteria with an array formula" - I am working with tables and address the columns with the table column names - the formula works however only if the criteria is YES for the first row in the table - if the criteria is not matched the total Sum is equal to Zero - it seems to disregard the other rows and does no sum them up. - any idea?

      I had to now add SUMIFS about 12 times which looks awful.. thanks a lot

      Nicole
    • To post as a guest, your comment is unpublished.
      GangnamMange · 5 months ago
      Sum all column values that has a specific value in the first column

      Cell A1: a
      Cell B1: {=SUM(--(A1=A3:A9)*B3:F9)} resulting in the sum 104 (has to be entered by using CTRL+SHIFT+ENTER)

      a 104

      a 2 5 7 2 2
      b 5 1 6 9 4
      c 7 6 1 1 9
      a 4 5 8 6 2
      b 4 8 3 2 6
      a 6 9 4 8 5
      a 6 8 9 5 1
    • To post as a guest, your comment is unpublished.
      belle · 1 years ago
      How can I sum multiple columns from single criteria/range?Can anyone advise using the below formula


      =sumif(T12:T69,>0,(sum(G13:G69)*sum(T12:T69)))
    • To post as a guest, your comment is unpublished.
      Erika · 2 years ago
      Can you help me get rid of the Div/0 in this formula. #DIV/0.

      =IF((F9+G9)>E65,E65/(F9+G9)*2,E65/(F9+G9+H9)*3)
      • To post as a guest, your comment is unpublished.
        Aria Anna · 2 years ago
        Hi Erika. Simply add the following:

        =iferror(your formula above, 0)