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

  • 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

Grumpy88

Grumpy88

Member
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:
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:
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:
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!!
 
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.
 
Not easily.
How about Remove Duplicates on the Data Tools section of the Data tab on the ribbon?
 
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.
 
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!
 
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.
 
You must log in or register to reply here.
Back
Top