CODEWITHSUNDEEP
excelformulaconditional-statements
Tauren
Tauren

Reputation: 37

multiple conditions in =sum

Phone number    Provider    Duration (min)
1001            Fastcom         2
1002            Fastcom         1
1004            Mobilecom       4
1008            Telecom         5
1001            Fastcom         3
1001            Fastcom         2
1003            Telecom         3
1004            Mobilecom       2
1008            Telecom         6
1002            Fastcom         1

I want to use one formula to get the sum of the duration of calls whose service provider is the provider for the phone number 1001. I hope this revised version is clearer than the previous one. Again, thanks to all who paid attention.

Upvotes: 2

Views: 173

Answers (2)

barry houdini
barry houdini

Reputation: 46341

There are 14 rows. Column A contains numbers from 1 to 14. Column B contains Names of people(maybe repeated). Column C contains their preferred seasons (possible more than 1) and Column D contains how many days they do sports in that partucular season. I need one formula to get how many days are spent doing sports in the season(s) that is (are) preferred by Adam, which actually are Spring and Fall

Assuming data in rows 2 to 15 try this array formula

=SUM(IF(ISNUMBER(MATCH(C2:C15,IF(B2:B15="Adam",C2:C15),0)),D2:D15))

confirmed with CTRL+SHIFT+ENTER

Upvotes: 5

Neil Barnwell
Neil Barnwell

Reputation: 42125

In one formula I'm not sure. Personally I'd have a formula in each row of column E that is 0 or the value from D, then you can sum(e:e) instead.

Upvotes: 1

Related Questions