Complex IF Statement in Excel

[RodS2]

I am trying to create a formula that will track total hours by person. The problem is that there could be multiple people in the same row. Here's an example

Description Who Time (days)
Paint car Door Sarah 4
Install Car Door Jack 12
Inspect Car Door Sarah/Robert 24


So, the function should check the "Who" column for a specific person, and if their name is in that column, that should include that associated time in the sum.

If I need to change how names are included on a line, like use a comma instead of a front slash I can do that. Also, the function should search the entire column for that persons name. So if B2:B4 = "Sarah" then add items from associated Time column to sum.


Thanks for your help!

 

[AnotherHiggins]

You should really change the way you are storing your data.

[tt][tab]Inspect Car Door Sarah/Robert 24[/tt]
should be broken down like this:
[tt][tab]Inspect Car Door Sarah 24
[tab]Inspect Car Door Robert 24[/tt]

But given your current data structure, the following should work:
[tab][COLOR=blue white]=SUMPRODUCT((ISNUMBER(SEARCH(D1, B2:B10))) * (C2:C10))[/color]
where D1 = Sarah

NOTE: This will look for the letters in the name anywhere in the cell. If you have an employee named Jim and another named Jimmy, you're in trouble.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[macropod]

Hi John,

To deal with the Jim/Jimmy scenario, all one needs is a second formula to deduct the 'Jimmy' results from the 'Jim*' results.

A more significant problem, though, is that the hours for Sarah & Robert have apparently been aggregated (ie they aren't each 24, but sum to 24). There's no way to extract either person's contribution to that total - the best one can do is to make some arbitrary assumption about the proportions each person contributed. Even so, you'll need different formulae to deal with the singular and aggregated hours.

Cheers

[MS MVP - Word]

 

[bluedragon2]

Macropod,

You can have another for Jim and Jimmy, but what about Dan, Danial, Danny, Dannie, Dane, etc...

I think John is correct with changing the way the data is stored. You will end up with less errors in the long run.

As far as the numbers go, one can only assume that both worked that amount of time on the project unless you store your data as John stated and break out the numbers...

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[ASG0856]

Try this;
=SUMIF(who column range,"the name of the person",time column range)
You would require one cell for each person

 

[pinkgecko]

If you have unlimited funding, you might want to look into Microsoft Project. It handles dependencies and time alottments and provides a lot more tools to work with that data than excel can.

[blue]When birds fly in the correct formation, they need only exert half the effort. Even in nature, teamwork results in collective laziness.[/blue]