Hi there and thanks in advance for any support provided.
I have a spreadsheet that captures the total number and type of requests a manager can give to an admin person. Usually, one request equals one task. However, on occasion, a single request can involve multiple instances of the same task. I can use a Countifs formula to calculate how many times a certain manager makes a certain request - and this works fine if we assume a single task per request. However, I can't figure out how to calculate when more than one task is completed per request?
By way of example I'm using this formula: =SUM(COUNTIFS($B$2:$B$26,"CEO",$C2:$C$26,{"Arrange Meeting"})) - adapted to cover all manager grades and all tasks.
A1 to D26 is the raw data. F1 to J5 are the calculations. This is an example spreadsheet only. The real version is much more complicated and with more data. See screenshot to see how it's being applied. How do I amend the formula to multiply the instance of the task by the number in column D if greater than 1? For instance; the Supervisor has requested photocopy 5 times but the task total in J5 should equal 37.
I'm sure it's very simple but has foxed me. I hope this makes sense and thanks again.
I have a spreadsheet that captures the total number and type of requests a manager can give to an admin person. Usually, one request equals one task. However, on occasion, a single request can involve multiple instances of the same task. I can use a Countifs formula to calculate how many times a certain manager makes a certain request - and this works fine if we assume a single task per request. However, I can't figure out how to calculate when more than one task is completed per request?
By way of example I'm using this formula: =SUM(COUNTIFS($B$2:$B$26,"CEO",$C2:$C$26,{"Arrange Meeting"})) - adapted to cover all manager grades and all tasks.
A1 to D26 is the raw data. F1 to J5 are the calculations. This is an example spreadsheet only. The real version is much more complicated and with more data. See screenshot to see how it's being applied. How do I amend the formula to multiply the instance of the task by the number in column D if greater than 1? For instance; the Supervisor has requested photocopy 5 times but the task total in J5 should equal 37.
I'm sure it's very simple but has foxed me. I hope this makes sense and thanks again.