1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula to sum in one column only for those rows with matching data in another column

Discussion in 'Ask an Excel Question' started by Grumpy88, Aug 11, 2017.

  1. Grumpy88

    Grumpy88 Member

    Messages:
    140
    Hi all.

    I am looking for the correct function to use that will add the values present in a number of rows in the same column, but only if those rows share the same value in another column. For example, Column A has a number of values, each of which repeat down several rows, while Column B has a different value in each of those rows.

    I have been playing with SUMIFS, but I can't figure out how to tell Excel as a criteria that it must only add the Column B values together if they share the same Column A value. My issue is that I don't have one specific criterion that I can offer, such as 13 or "North".

    I should add that, as there are hundreds of unique values in Column A, I'm looking for a summary-type formula that I can copy down and arrange the results in order from highest to lowest, pretty much as a pivot table might do it. I would like to avoid using a pivot table though, as I'd prefer a formula-based approach that immediately displays what I need on the worksheet automatically.

    Any pointers please?

    Thanks.
    Last edited: Aug 11, 2017
  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,069
    Hi ,

    Upload a workbook please.

    Narayan
  3. Grumpy88

    Grumpy88 Member

    Messages:
    140
    Okay, thanks.

    I can't get the workbook small enough to upload, so I've included a screenshot instead.

    upload_2017-8-11_14-40-7.png

    Column C has an identifying number that repeats over multiple rows, effectively making it a category for each of the numerous persons and their results (given that in many cases, the same person has more than one result for the same Column C ID number).

    I need a summarising formula that will add together the Column D results for each person, per specific Column C ID number. I then want to arrange that list of formula results from highest to lowest. So, for example, the formula should run through all the events and tell me that person RE Davidson achieved a 22 score from event 1, CW Mellors is second with 120, E Halvorsen third with 101, etc. (You can't see that from the screenshot posted, but that data comes from a pivot table that I used on the actual workbook. However, ideally I'd like to avoid using a pivot table, and instead have all the results set out in neat tabular format in a single workbook of formulas from which I can copy and paste directly).

    Could you help?
    Last edited: Aug 11, 2017
  4. p45cal

    p45cal Well-Known Member

    Messages:
    763
    In cell E2 (or any cell in row 2):
    =SUMIF($C$2:$C$57,C2,$D$2:$D$57)
    with the 57s adjusted to suit your range.

    With more granularity:
    =SUMIFS($D$2:$D$57,$C$2:$C$57,C2,$B$2:$B$57,B2)
    Last edited: Aug 11, 2017
    Thomas Kuriakose and Grumpy88 like this.
  5. Grumpy88

    Grumpy88 Member

    Messages:
    140
    That is all kinds of awesome, thank you 945cal!

    I have added that as a helper column to my spreadsheet, and it works beautifully! I will now use it by referencing that helper column in the worksheet where I want the formula summary to be for reporting purposes.

    Gotta love this forum!!
  6. Grumpy88

    Grumpy88 Member

    Messages:
    140
    Sorry, just one slight tweak still needed actually, please.

    As the same name often appears twice for the same Column C event, the combined value of that person's Column D scores appears next to each instance of that person's name when I copy the formula down, such that each total is reported on twice.

    Is there a way to alter the formula slightly so that it ignores duplicates / only calculates once for each person / event combination?

    Thanks.
  7. p45cal

    p45cal Well-Known Member

    Messages:
    763
    Not easily.
    How about Remove Duplicates on the Data Tools section of the Data tab on the ribbon?
  8. Grumpy88

    Grumpy88 Member

    Messages:
    140
    Thanks, but I think that'd wreak havoc across the rest of the spreadsheet.

    Slightly disappointing, but not that serious - I'd rather have double reporting that I can manually edit than no reporting at all.

    Appreciate your efforts very much.
  9. AliGW

    AliGW Member

    Messages:
    61
    You could do this sort of thing:

    =IF(COUNTIFS($A$1:$A1,A1,$B$1:$B1,B1)>1,"",(SUMIFS($C$1:$C$24,$A$1:$A$24,A1,$B$1:$B$24,B1)))

    upload_2017-8-11_16-23-23.png
    Thomas Kuriakose likes this.
  10. Grumpy88

    Grumpy88 Member

    Messages:
    140
    My Excel saviour returns!! I'm on my way home now, but in great faith will make sure to check that out when I get there.

    Thank you!
  11. AliGW

    AliGW Member

    Messages:
    61
    You're welcome! Hope it helps. :)
  12. Grumpy88

    Grumpy88 Member

    Messages:
    140
    Hi Ali.

    Ended up having a rather busy weekend, to the extent that I didn't touch my laptop at all. I therefore have only now tried out your formula suggestion.

    I'm very happy to announce that it (once again) worked perfectly for me. I wish I could hire you to create my entire spreadsheet for me. When you're not on vacation, I hope your manager appreciates your amazing Excel skills!

    Thanks so much once again.
  13. AliGW

    AliGW Member

    Messages:
    61
    You're welcome! :)

Share This Page