Summing with multiple criteria

Schannah

New Member
Joined
Apr 18, 2016
Messages
3
Hi there,

I'm trying to come up with a formula that will allow me to compare the race results of teammates in a certain sport.

What I need is a way of finding out the sums of a) both teammates' results and b) only one teammate's results in races that they both finished.

This is quite hard to explain, so I will give a condensed example:

AUSBHRCHN
Red BullRicciardo444
FerrariVettel3DNS2
FerrariRäikkönenRet25
Red BullKvyatDNS73
McLarenVandoorne10
McLarenAlonsoRet12
McLarenButton14Ret13

<tbody>
</tbody>

So I want two formulae that will look through my table and find out which races were finished by both teammates (e.g. Ferrari), and then give me:
  • across all of those races, the sum of the two teammates' combined results
  • across all of those races, the sum of the individual's results

Any non-numerical values (DNS, blanks, Ret, etc.) indicate that the driver did not finish a race.

(For those unfamiliar with the sport in question, a maximum of two drivers per team compete in any given race. However, it may be the case (as with McLaren) that more than two drivers represent the team over the course of several races, e.g. because one of the main two drivers is injured and has to be replaced with a reserve driver for the same team. So although you will only ever be looking at two rows per team for each race, the exact references of those rows may vary. For that reason, and in order to add in and move around drivers/teams easily, I want the formula to be capable of identifying which rows it needs to look at for each race.)

e.g. For Vettel, who drives for Ferrari, first we must identify that the only race finished by both Ferrari drivers (whoever they were at the time) is China. The first formula should give the sum of both Ferrari drivers' results in China: 2+5 = 7. The second formula should show that the sum of Vettel's results in China is 2.
For Ricciardo, the races finished by both drivers were Bahrain and China. The first formula should give 4+4+7+3=18 and the second should give 4+4=8.

The closest I have been able to get to this is a formula that calculates the sum of all the team results in races finished by the individual driver, and then deducts the individual driver's results - thereby giving the sum of the driver's teammate's results in all races finished by them both. I have done this using: SUMPRODUCT($E$3:$G$9,($B$3:$B$9=B3)*ISNUMBER($E$3:$G$9)*ISNUMBER(E3:G3))-SUM(E3:G3)
where: the formula relates to the individual to whom row 3 relates
B3:B9 is the range containing team names
E3:G9 is the range containing all drivers' race results
E3:G3 is the range containing only the race results for the individual named in row 3.

This is a bit of a bodge and I can't think of a way of applying the same logic used here to a similar formula that gives me the results I want. I'm sure that the right formula will be either an array formula or a SUMPRODUCT of some kind, but for the life of me I can't figure out how to get there.

(The plan is to use these numbers to calculate certain comparative statistics, such as the mean difference in finishing positions between driver X and his teammate across all races in which both driver X and his teammate finished.)

Hopefully I've explained the issue and required outcome properly. Any ideas?
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
without reading all that, maybe look at sumifs function? It does multiple criteria
 

Schannah

New Member
Joined
Apr 18, 2016
Messages
3
Sorry, that doesn't really help. I can't see how SUMIFS would be used in this situation. I appreciate I've written a lot but the actual query is contained in the first couple of lines - the rest is just illustration to explain what it means. If someone does read what I'm asking for and have any ideas, I'd be very grateful. But otherwise, responding without reading the question just makes it look like the problem is resolved when it isn't!
 

davidamiri

New Member
Joined
Mar 25, 2016
Messages
3
Sorry, that doesn't really help. I can't see how SUMIFS would be used in this situation. I appreciate I've written a lot but the actual query is contained in the first couple of lines - the rest is just illustration to explain what it means. If someone does read what I'm asking for and have any ideas, I'd be very grateful. But otherwise, responding without reading the question just makes it look like the problem is resolved when it isn't!


Possibly if you use if statements nested within the sumproduct for the formula to multiply only when there is a number , if it is text, then it will not calculate it. Also can use Index and match to create an array for the two teammates, nest that in the also somehow. Just a thought. Thanks.
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
If I understand you correctly, for the overall total, use this ARRAY formula...
=SUM(IF($A$2:$A$8="ferrari",$C$2:$E$8))
entered using CTRL: SHIFT ENTER, not just enter
 

Watch MrExcel Video

Forum statistics

Threads
1,129,923
Messages
5,639,024
Members
417,063
Latest member
ShijinMathew

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top