# Summing with multiple criteria

#### Schannah

##### New Member
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?

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### Roderick_E

##### Well-known Member
without reading all that, maybe look at sumifs function? It does multiple criteria

#### Schannah

##### New Member
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
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
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

##### New Member
The Match and Isnumber formula also comes to mind.

Replies
3
Views
388
Replies
1
Views
224
Replies
2
Views
171
Replies
8
Views
144
Replies
3
Views
508

1,128,113
Messages
5,628,777
Members
416,338
Latest member
Spartan5305

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

### Which adblocker are you using?

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

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