Summing with multiple criteria

[Schannah]

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:

		AUS	BHR	CHN
Red Bull	Ricciardo	4	4	4
Ferrari	Vettel	3	DNS	2
Ferrari	Räikkönen	Ret	2	5
Red Bull	Kvyat	DNS	7	3
McLaren	Vandoorne		10
McLaren	Alonso	Ret		12
McLaren	Button	14	Ret	13

<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?

 

[Roderick_E]

without reading all that, maybe look at sumifs function? It does multiple criteria

 

[Schannah]

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]

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]

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

 

[davidamiri]

The Match and Isnumber formula also comes to mind.