COUNTIF/SUMIF items list or Summary of the data

Hello,

I have an excel with a lot of data (below listed 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, so I am listing the data as below & the picture with yellow cells is how i would like the result to be. Thanks in advance for your time.

Task Project Priority Resource Status Effort
CAD P-01234 Medium Ramesh In Progress
CAD P-01235 Low Krish In Progress
CAD P-01236 Medium Mani Completed 3
CAD P-01237 High Mani In Progress 3
CAD P-01238 Medium Mani In Progress
CAD P-01239 Medium Mani In Progress 3
Design P-01240 Medium Mani In Progress 4
CAD P-01250 Medium Mani In Progress
Report P-01251 Medium Mani Completed 12
Design P-01252 Medium Mani In Progress 34
Report P-01253 Low Krish In Progress 45
CAD P-01239 Medium Mani On Hold
CAD P-01240 High Mani Completed 34
Report P-01247 High Mor Completed
Report P-01248 Medium Mor In Progress 6
Design P-01249 Medium Mor In Progress
CAD P-01250 High Mor In Progress 99
CAD P-01251 Medium Mor In Progress 1
CAD P-01252 High Mor Completed 2
CAD P-01253 Low Krish In Progress 23
CAD P-01254 Medium Krish On Hold 34
Design P-01255 Medium Ramesh Completed 45
Report P-01256 High Mor In Progress 34
Design P-01257 Medium Mani Completed
Report P-01237 Medium Ramesh Completed 23
CAD P-01238 Medium Mor Completed
CAD P-01239 High Bari Completed 8
CAD P-01250 Medium Bari Completed
CAD P-01251 Medium Bari Completed 23
CAD P-01252 Medium Suba In Progress 2
Design P-01253 Medium Suba In Progress
Report P-01265 Medium Suba In Progress 4
Design P-01265 Medium Suba In Progress 5
CAD P-01265 Medium Suba In Progress 4


Table.png
Desired result.png

Hello,

Thanks for the guidance. I have now attached the Excel.
The yellow cells shown in Excel is the dummy result that i want to achieve. Thanks.

Sorry,
My mistake. The numbers in the results table for Ramesh was completely made up since i just wanted to show how the results should show up.
Now, i have added the results table for SUBA which is based on the values in the table. Hope this makes sense. Thanks.

Hello Bo_Ry,

With the formula you provided, i get the totals for each of the projects. However, i do not know the projects related to an associate to begin with. I only know the resource name.
If i use COUNTIF (some thing like this --> COUNTIF($K$24:$K$2999,AD19,$N$24:$N$2999,"<>"), i get the total number of columns for a resource that have efforts in them.
Now, i want to know the project numbers in these 7 rows and then check if some of these projects are same (just like the last 3 rows in the excel that i have attached). If not, then i want to list all these projects and the efforts against each of these projects.
If the project numbers repeat, then i want to show that project only once & all the effort against that also once. That is what i have done with the dummy result for Suba.
Thanks

In the attached Excel, i have put in the COUNTIFS & SUMIF to get the number of rows which have effort values for a particular resource & total effort for a resource respectively.
As mentioned earlier, i want to know the project numbers in these 6 rows and then check if some of these projects are same (just like the last 3 rows in the excel that i have attached). If not, then i want to list all these projects and the efforts against each of these projects.
If the project numbers repeat, then i want to show that project only once & all the effort against that also once. That is what i have done with the dummy result for Suba.

Thanks for your help in advance.

What about a grid, project ID down, individual resort across? Then SUMIFS does the job.

Hi Bo_Ry,

Thanks for your help. Pivot table may work. I will try on the actual data (it is not well structured as the table i provided but nonetheless, i have a general idea now).

Hi Bo_Ry,

The functions work better that the Pivot table for me. However, i don't want the projects which have effort "0" showing. How do i accomplish that?

When i set the custom format to #, Wherever the effort is 0, that does not show up. However, the project number still shows up (in column J) as shown in the picture.
I do not want the project number to show up either because there are too many project where the effort is not that and if all of them show up, it will just eat the real estate on the spreadsheet.

2019-02-12_11h37_12.png

It worked
Thank you very very much.