I had a spreadsheet with several columns
J - a person who will do the task
E - an estimate of time to perform something
K - the phase that the task is going to do something
L - column l was hidden and contained values like = j1 & k1 - it was used for lookup of how much a person was working on a specific task.
I wanted to summerize the tasks to come up with
- who is working on the project.
- at which phase is that person needed for that project
- how much resources will be needed. (Both all resources, and resource per phase)
The Person
b423=INDEX(J$3:$J408, MATCH(0, COUNTIF($B$422:$B422,J$3:$J408),0))
J$3:$J408 - person assigned to the task
$B$422:$B422 - peopel we have evaluated so far
J$3:$J408 - person assigned to the task
– how much time is needed for a resource
e390=SUMIF($J$3:$J374,$J390,$E$3:$E374)
$j$3 - $j390 - person assigned to the task ** Range of critera data **
$j390 - person who we are looking at ** Criteria value - can be “x”, or “=x”, or “>421”, etc…
$e$3-$e$374 - estimate of time - ** These contain the numbers that will be added ** ** if this argument is not used, then it will use the first range instead**
– how much time was spent on a phase
e423=SUMIF($K$3:K$374,$A423,E$3:E$374)
k3-k374 - phase of each task
$a423 - phase we are working on
e3-e384 - estimate of the time
=SUMIF($L$3:$L$374,$A$421 & $B423,E$3:E$374)
$L$3:$L$374 - concat of phase, person performing the task
$A$421 & $B423 - person we are examining
E$3:E$374 - time spent by that person task
=SUMIF (Range, Criteria, [SumRange])
=CountIf (Range, Criteria)
=Index(array, row_num, [column_num]) Array form
=index(reference, rownum, [column_num], [area_num]) reference form
=match(lookup_value, lookup_array, [match_type])
matchtype=-1, 0, 1 (default 1)
1 - largest value <= value in lookup value
0 - first value = lookup value
-1 - smallest value that is >= lookup value
Dont forget Control / Shift / Enter when recording calculations.
Credits
https://exceljet.net/excel-functions/excel-sumif-function