Hello,
I have an excel with a lot of data (attached excel is just a small portion of it).
In this excel, my primary objective is to find the EFFORT by each RESOURCE to each PROJECT.
If i use SUMIF, i get the total effort for each resource easily.
However, I want to know list the PROJECTS and respective EFFORT for each RESOURCE (if effort for a particular PROJECT has zero EFFORT by an associate, then that project need not be listed)
Below is the sample of the data I have. Full sheet has too many values like this (one more reason why i don't want the PROJECT that zero EFFORT by an associate need not be listed)
For some reason, i am unable to attache the file. Thanks in advance for your time.
[TABLE="width: 455"]
<tbody>[TR]
[TD]Task[/TD]
[TD]Project[/TD]
[TD]Priority[/TD]
[TD]Resource[/TD]
[TD]Status[/TD]
[TD]Effort[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01234[/TD]
[TD]Medium[/TD]
[TD]Ramesh[/TD]
[TD]In Progress[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01235[/TD]
[TD]Low[/TD]
[TD]Krish[/TD]
[TD]In Progress[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01236[/TD]
[TD]Medium[/TD]
[TD]Mani[/TD]
[TD]Completed[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01237[/TD]
[TD]High[/TD]
[TD]Mani[/TD]
[TD]In Progress[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01238[/TD]
[TD]Medium[/TD]
[TD]Mani[/TD]
[TD]In Progress[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01239[/TD]
[TD]Medium[/TD]
[TD]Mani[/TD]
[TD]In Progress[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Design[/TD]
[TD]P-01240[/TD]
[TD]Medium[/TD]
[TD]Mani[/TD]
[TD]In Progress[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01250[/TD]
[TD]Medium[/TD]
[TD]Mani[/TD]
[TD]In Progress[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Report[/TD]
[TD]P-01251[/TD]
[TD]Medium[/TD]
[TD]Mani[/TD]
[TD]Completed[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Design[/TD]
[TD]P-01252[/TD]
[TD]Medium[/TD]
[TD]Mani[/TD]
[TD]In Progress[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]Report[/TD]
[TD]P-01253[/TD]
[TD]Low[/TD]
[TD]Krish[/TD]
[TD]In Progress[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01239[/TD]
[TD]Medium[/TD]
[TD]Mani[/TD]
[TD]On Hold[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01240[/TD]
[TD]High[/TD]
[TD]Mani[/TD]
[TD]Completed[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]Report[/TD]
[TD]P-01247[/TD]
[TD]High[/TD]
[TD]Mor[/TD]
[TD]Completed[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Report[/TD]
[TD]P-01248[/TD]
[TD]Medium[/TD]
[TD]Mor[/TD]
[TD]In Progress[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Design[/TD]
[TD]P-01249[/TD]
[TD]Medium[/TD]
[TD]Mor[/TD]
[TD]In Progress[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01250[/TD]
[TD]High[/TD]
[TD]Mor[/TD]
[TD]In Progress[/TD]
[TD]99[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01251[/TD]
[TD]Medium[/TD]
[TD]Mor[/TD]
[TD]In Progress[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01252[/TD]
[TD]High[/TD]
[TD]Mor[/TD]
[TD]Completed[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01253[/TD]
[TD]Low[/TD]
[TD]Krish[/TD]
[TD]In Progress[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01254[/TD]
[TD]Medium[/TD]
[TD]Krish[/TD]
[TD]On Hold[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]Design[/TD]
[TD]P-01255[/TD]
[TD]Medium[/TD]
[TD]Ramesh[/TD]
[TD]Completed[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]Report[/TD]
[TD]P-01256[/TD]
[TD]High[/TD]
[TD]Mor[/TD]
[TD]In Progress[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]Design[/TD]
[TD]P-01257[/TD]
[TD]Medium[/TD]
[TD]Mani[/TD]
[TD]Completed[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Report[/TD]
[TD]P-01237[/TD]
[TD]Medium[/TD]
[TD]Ramesh[/TD]
[TD]Completed[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01238[/TD]
[TD]Medium[/TD]
[TD]Mor[/TD]
[TD]Completed[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01239[/TD]
[TD]High[/TD]
[TD]Bari[/TD]
[TD]Completed[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01250[/TD]
[TD]Medium[/TD]
[TD]Bari[/TD]
[TD]Completed[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01251[/TD]
[TD]Medium[/TD]
[TD]Bari[/TD]
[TD]Completed[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01252[/TD]
[TD]Medium[/TD]
[TD]Suba[/TD]
[TD]In Progress[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Design[/TD]
[TD]P-01253[/TD]
[TD]Medium[/TD]
[TD]Suba[/TD]
[TD]In Progress[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Report[/TD]
[TD]P-01265[/TD]
[TD]Medium[/TD]
[TD]Suba[/TD]
[TD]In Progress[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Design[/TD]
[TD]P-01265[/TD]
[TD]Medium[/TD]
[TD]Suba[/TD]
[TD]In Progress[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01265[/TD]
[TD]Medium[/TD]
[TD]Suba[/TD]
[TD]In Progress[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]
I have an excel with a lot of data (attached excel is just a small portion of it).
In this excel, my primary objective is to find the EFFORT by each RESOURCE to each PROJECT.
If i use SUMIF, i get the total effort for each resource easily.
However, I want to know list the PROJECTS and respective EFFORT for each RESOURCE (if effort for a particular PROJECT has zero EFFORT by an associate, then that project need not be listed)
Below is the sample of the data I have. Full sheet has too many values like this (one more reason why i don't want the PROJECT that zero EFFORT by an associate need not be listed)
For some reason, i am unable to attache the file. Thanks in advance for your time.
[TABLE="width: 455"]
<tbody>[TR]
[TD]Task[/TD]
[TD]Project[/TD]
[TD]Priority[/TD]
[TD]Resource[/TD]
[TD]Status[/TD]
[TD]Effort[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01234[/TD]
[TD]Medium[/TD]
[TD]Ramesh[/TD]
[TD]In Progress[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01235[/TD]
[TD]Low[/TD]
[TD]Krish[/TD]
[TD]In Progress[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01236[/TD]
[TD]Medium[/TD]
[TD]Mani[/TD]
[TD]Completed[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01237[/TD]
[TD]High[/TD]
[TD]Mani[/TD]
[TD]In Progress[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01238[/TD]
[TD]Medium[/TD]
[TD]Mani[/TD]
[TD]In Progress[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01239[/TD]
[TD]Medium[/TD]
[TD]Mani[/TD]
[TD]In Progress[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Design[/TD]
[TD]P-01240[/TD]
[TD]Medium[/TD]
[TD]Mani[/TD]
[TD]In Progress[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01250[/TD]
[TD]Medium[/TD]
[TD]Mani[/TD]
[TD]In Progress[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Report[/TD]
[TD]P-01251[/TD]
[TD]Medium[/TD]
[TD]Mani[/TD]
[TD]Completed[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Design[/TD]
[TD]P-01252[/TD]
[TD]Medium[/TD]
[TD]Mani[/TD]
[TD]In Progress[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]Report[/TD]
[TD]P-01253[/TD]
[TD]Low[/TD]
[TD]Krish[/TD]
[TD]In Progress[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01239[/TD]
[TD]Medium[/TD]
[TD]Mani[/TD]
[TD]On Hold[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01240[/TD]
[TD]High[/TD]
[TD]Mani[/TD]
[TD]Completed[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]Report[/TD]
[TD]P-01247[/TD]
[TD]High[/TD]
[TD]Mor[/TD]
[TD]Completed[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Report[/TD]
[TD]P-01248[/TD]
[TD]Medium[/TD]
[TD]Mor[/TD]
[TD]In Progress[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Design[/TD]
[TD]P-01249[/TD]
[TD]Medium[/TD]
[TD]Mor[/TD]
[TD]In Progress[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01250[/TD]
[TD]High[/TD]
[TD]Mor[/TD]
[TD]In Progress[/TD]
[TD]99[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01251[/TD]
[TD]Medium[/TD]
[TD]Mor[/TD]
[TD]In Progress[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01252[/TD]
[TD]High[/TD]
[TD]Mor[/TD]
[TD]Completed[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01253[/TD]
[TD]Low[/TD]
[TD]Krish[/TD]
[TD]In Progress[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01254[/TD]
[TD]Medium[/TD]
[TD]Krish[/TD]
[TD]On Hold[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]Design[/TD]
[TD]P-01255[/TD]
[TD]Medium[/TD]
[TD]Ramesh[/TD]
[TD]Completed[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]Report[/TD]
[TD]P-01256[/TD]
[TD]High[/TD]
[TD]Mor[/TD]
[TD]In Progress[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]Design[/TD]
[TD]P-01257[/TD]
[TD]Medium[/TD]
[TD]Mani[/TD]
[TD]Completed[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Report[/TD]
[TD]P-01237[/TD]
[TD]Medium[/TD]
[TD]Ramesh[/TD]
[TD]Completed[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01238[/TD]
[TD]Medium[/TD]
[TD]Mor[/TD]
[TD]Completed[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01239[/TD]
[TD]High[/TD]
[TD]Bari[/TD]
[TD]Completed[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01250[/TD]
[TD]Medium[/TD]
[TD]Bari[/TD]
[TD]Completed[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01251[/TD]
[TD]Medium[/TD]
[TD]Bari[/TD]
[TD]Completed[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01252[/TD]
[TD]Medium[/TD]
[TD]Suba[/TD]
[TD]In Progress[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Design[/TD]
[TD]P-01253[/TD]
[TD]Medium[/TD]
[TD]Suba[/TD]
[TD]In Progress[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Report[/TD]
[TD]P-01265[/TD]
[TD]Medium[/TD]
[TD]Suba[/TD]
[TD]In Progress[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Design[/TD]
[TD]P-01265[/TD]
[TD]Medium[/TD]
[TD]Suba[/TD]
[TD]In Progress[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01265[/TD]
[TD]Medium[/TD]
[TD]Suba[/TD]
[TD]In Progress[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]
Last edited: