Variation of Sumif?

[Mike Rose]

Trying to create a spreadsheet for my crews. Top of Column A has my crew names, B has which crews are assigned to particular sites (Column C), and D has how many hours were spent on this site.

Problem: Want to create a chart (see bottom part) which shows how many hours were worked by each crew. It would be a simple sumif formula (I only know how to do exact value) if crews did not double up on sites (See lines 13-15). What (and how) do I set up a formula that captures the hours for a crew when there are multiple crews on that site?

 

[tomas]

I don't understand to which crew names should be assigned hours worked for crews on site in cells B13:B15

 

[Mike Rose]

I don't understand to which crew names should be assigned hours worked for crews on site in cells B13:B15

There are only ten crews in this example - they are listed in the first column.

There are more sites than crews. Some of the sites are being done by multiple crews.

I need a formula that tallys the hours whenever a crew's number is in that column.

Example: Cell B21 should tally any hours for crew 1 that works on any of the sites above. In this case, there are two. B3 & B13.

 

[NARAYANK991]

Hi ,

What is the maximum number of crews that could possibly be working at the same site ?

Narayan

 

[Mike Rose]

3

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[Mike Rose]

I think so - need to try and duplicate it into my work to see. Thank you.