CODEWITHSUNDEEP
excelgoogle-sheetsvlookup
veilig
veilig

Reputation: 5135

spreadsheet find multiple matches

I have a spreadsheet for runners with multiple pages

Van and runner information

I'm trying to automatically calculate the total mileage for each runner (in columns D an H in the Vans sheet) depending what legs that runner is assigned from the Legs sheet.

Leg breakdown

This was working fine since each runner was only going to be running in a predefined range, so my initial formula in the vans sheet worked. But now I need to move runners around into different blocks and I'm trying to find a more general formula that can figure this out w/out saying only find this runner in the three ranges I defined.

So my question is: Is there a forumula that can use on the Vans sheet, that will take that runners name - then look through and find all matches in the Legs sheet (column D) - then add up the distance (column G) for those matching rows?

ie: in my 2nd picture of (Legs sheet), runner 1 is in cell D2 and D20, so his total mileage would be 5.53 + 4.1 = 9.63 . (G2 + G20)

Upvotes: 0

Views: 138

Answers (2)

Ed Nelson
Ed Nelson

Reputation: 10259

In Google Spreadsheets you can use arrayformula sumif to sum the runners. 

=arrayformula(if(A2:A<>"",sumif(Legs!D2:G,A2:A,Legs!G2:G),""))

Upvotes: 0

jkpieterse
jkpieterse

Reputation: 2956

Check out the various ***IFS functions built into Excel (which ones you have available depends on your Excel version):

SUMIFS COUNTIFS AVERAGEIFS MAXIFS MINIFS Those might fit your needs.

Upvotes: 2

Related Questions