Sum range if cells contain text, with variables

hugo_goes_woof

New Member
Joined
Nov 16, 2016
Messages
2
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:

ABCDE
1Total HoursWeek 1Week 2Weeks 3
2Task 140A
3Task 260A
4Task 390A
5Task 440A
61004090

<tbody>
</tbody>

In the above example, my formula for the Week 1 weekly total in cell C6 is:
Code:
=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.

ABCDE
1Total HoursWeek 1Week 2Weeks 3
2Task 140A
3Task 260AA
4Task 390A
5Task 440AA
65060120

<tbody>
</tbody>

In the above example, my formula for the Week 1 weekly total in cell C6 is:
Code:
=(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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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).
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top