Need help with SUMIFS formula

Here is my scenario: I have a list of competitors with their dogs and the points they've earned in competitions. I would like to be able to add up all the points earned by each handler/dog combination and display it in a list of descending order in another sheet.

I am currently using this formula =IF(F2=F1,"",SUMIF(F:F,F2,G:G)), which seemed to work once the list was sorted into order by the name of the dog. But, I just discovered that it's adding points together for all the dogs with the same name, regardless of the handler. So, I need to make a formula to tally the points for the dogs in column F and compare the handler name in Column E and put the correct total with the correct handler/dog combination.
Essentially, I need a total of all points in column G, where Column E and Column F are the same combination.

The SUMIFS formula seemed to be the answer and I've tried using it, but I apparently can't get the syntax right because I keep getting a 0 result.

Column E - Column F - Column G

Jim Smith - Slink - 20
Dave Jones - Mouse -120
Jim Smith - Slik - 220
Kate North - Blue - 320
Ed Walker - Dave - 420
Jim Davis - Dave - 60
Sam Smith - Liz - 160
Sam Smith - Pete - 360
Char Edward - Tuff - 460
Joe Nellis - Bullet - 560
Brett Gaines - Sassy - 660
Tom Williams- Duncan- 60

as I said: show expected result

but I'll try again

Brett Gaines	Sassy	660
Char Edward	Tuff	460
Dave Jones	Mouse	120
Ed Walker, Jim Davis	Dave	480
Jim Smith	Slink	20
Jim Smith	Slik	220
Joe Nellis	Bullet	560
Kate North	Blue	320
Sam Smith	Pete	360
Sam Smith	Liz	160
Tom Williams	Duncan	60

I did show the result I'm looking for, it's in my reply. Hang on, I'll post a bigger list to work from

the best way is attach example excel file with source and expected result

To attach an Excel file to your post,

• desensitize data
• remeber that your example should reflect structure and type of data and contain the result what you want to achieve (manually created if necessary)
• click Go Advanced,
• scroll down until you see Manage Attachments,
• click that and select Browse,
• select your file and click Open,
• click Upload and you will see your attachment below Upload Files from a website
• click Close this window,
• click Submit reply

After that you should see attachment in your post

Ok, I will sort it up into a new sheet and post that file. Thank you

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Excel file attached

I think my original data clip was too small to illustrate what I was wanting to do. I've sorted it manually and posted that file to a reply

you sorted points
but you didn't show expected result.

if your sorted range is an expected result what you want more?

I have attempted to use a Pivot Table, but it gives me subtotals for each handler name instead of a list that I can sort into a descending order. I've also looked into using the Match function, but I don''t think that will do it either.

I will look into the Power Query

in PivotTable you can turn on/off subotals, total, etc....
click on PivotTable, ribbon, Design, go to the very left of the ribbon and you will see there what you can do

Ahh, this is SO damned frustrating!

Why can't I just write a formula that will tell it to look at Column A and Column B, compare them to each other, then add up the numbers that are in Column C that correspond to each unique pair of text items in Column A And Column B????? Isn't that what the SUMIFS function is supposed to do?

I can look at the data and see what needs to be done to accomplish it, but this very powerful spreadsheet is incapable of doing that very thing?

And, in the Pivot Table, the subtotals are what I need, in a list. I don't need the total points per handler.


Thanks very much for your help so far, if you want to bail out, I don't blame you. I'm about ready to myself.....

Found the solution!!

Here's what I finally got resolved, turned out I was missing identifying the specific cell to compare in each column.

=SUMIFS(C:C,B:B,B4,A:A,A4)
Then, reference that to Sheet2, =SUMIFS(Sheet1!C:C,Sheet1!B:B,Sheet1!B4,Sheet1!A:A,Sheet1!A4), sort for descending value, hide the duplicates, then print the list


Thank you for the help, it kept me digging for the answer when I was about ready to pack it in.