SUMIF? COUNTIF? Combining percentages IF there is a value...

wilsonldn

New Member
Hello MrExcel Community!

Thanks in advance for all of your input, I always seem to get a great response to my questions here.

So I have created a table, that looks at a live feed and measures staff on the completion of their projects. The projects are split into two groups BAU and non BAU, I then calculate their over all percentage of completion for both groups. I would like to combine these percentages however I cannot get the sum to take into consideration that some values may be "0", and if so, no further calculation would be needed.

 # BAU Projects # Non-BAU Projects Total Projects BAU % Non-BAU % Total % Matthew 1 1 2 100.00% 50.00% Mark 2 0 2 95.00% 0.00% Luke 3 2 5 75.00% 60.00% John 0 3 3 0.00% 80.00%

<TBODY>
</TBODY><COLGROUP><COL span=2><COL><COL span=3><COL></COLGROUP>

Above is come example data...

So in the total percentage column, i would want results that for Mark and John, take into consideration they have "0" for some projects, and thus do not divide by 2, but for Matthew and Luke, do divide by two as there are two entries.

75% for Matthew
95% for Mark
67.5% for Luke
& 80% for John

Do let me know if I am trying to do this the long way round!

Wilson

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
And then it hit me in the face...

Ok, so I have created another column to "countif >0" and will use that to determine what the sum should divide by!

Replies
0
Views
807
Replies
2
Views
622
Replies
9
Views
319
Replies
1
Views
716
Replies
6
Views
362

1,203,674
Messages
6,056,680
Members
444,881
Latest member
Stu2407

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.

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

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