Sum range if cells contain text, with variables

[hugo_goes_woof]

Hello all,

I am writing a spreadsheet for staff to program their hours against a list of tasks.

The spreadsheet has a list of tasks (column A) and the corresponding total hours required to complete each task (column B).
In the Week "X" cells C2:E5, the user inputs a letter (in this example, "A")* to indicate which week(s) the task is to be completed.
The totals on the bottom row C6:E6 then show the total hours required to complete all tasks for that week - this is where I need help.

The above is easy to do if each task is only assigned to a single week, as per the example below:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Total Hours[/TD]
[TD]Week 1[/TD]
[TD]Week 2[/TD]
[TD]Weeks 3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Task 1[/TD]
[TD]40[/TD]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Task 2[/TD]
[TD]60[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Task 3[/TD]
[TD]90[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Task 4[/TD]
[TD]40[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD]40[/TD]
[TD]90[/TD]
[/TR]
</tbody>[/TABLE]

In the above example, my formula for the Week 1 weekly total in cell C6 is:

=SUMIF(C2:C5,"*",($B2:$B5))

This formula has two easy-to-check ranges, so inserting rows for additional tasks is also simple to troubleshoot for other users.


My problem is, if a particular task is to be completed over more than one week as per the example below, the weekly totals need to average the hours of that task over the number of weeks (ie, completing a 40-hour task over 2 weeks means averaging 20 hours each week). I don't know how to do this in a concise way.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Total Hours[/TD]
[TD]Week 1[/TD]
[TD]Week 2[/TD]
[TD]Weeks 3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Task 1[/TD]
[TD]40[/TD]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Task 2[/TD]
[TD]60[/TD]
[TD]A[/TD]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Task 3[/TD]
[TD]90[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Task 4[/TD]
[TD]40[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]60[/TD]
[TD]120[/TD]
[/TR]
</tbody>[/TABLE]

In the above example, my formula for the Week 1 weekly total in cell C6 is:

=(IF(ISTEXT(C2),SUM($B2/COUNTIF($C2:$E2,"*")),0))
+(IF(ISTEXT(C3),SUM($B3/COUNTIF($C3:$E3,"*")),0))
+(IF(ISTEXT(C4),SUM($B4/COUNTIF($C4:$E4,"*")),0))
+(IF(ISTEXT(C5),SUM($B5/COUNTIF($C5:$E5,"*")),0))

Obviously this consists of summing multiple instances of formulas. While the sums are correct, it seems overly long and clumsy; and inserting rows for additional tasks means you need to manually type in another instance of the formula, which is not easy for the standard user here. In the full spreadsheet, there may be up to 40-50 tasks, making the formula ridiculously long. There must be a neater way!


*Note re the letter: in the full spreadhseet, each team working on the job is assigned a letter (Team A, Team B etc) and the total number of tasks, hours to complete etc are summed elsewhere by counting these letters; and conditional formatting colour-codes the tasks based on the team letter. So the letters can't be substituted for, say, numerical values.
**Using Excel2010/Win7

 

[XOR LX]

Hi.

Array formula** in C6:

=SUM(IF(C2:C5<>"",$B2:$B5/MMULT(0+($C2:$E5<>""),TRANSPOSE(COLUMN($C2:$E5))^0)))

Copy across as required.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[hugo_goes_woof]

=SUM(IF(C2:C5<>"",$B2:$B5/MMULT(0+($C2:$E5<>""),TRANSPOSE(COLUMN($C2:$E5))^0)))

That's exactly what I was after. Solved, thanks!

 

[XOR LX]

You're welcome!

Cheers