# How to calculate a percentage of completed task in this spreadsheet?

I have the following spreadsheet. Basically Sam and Heather split out their assignment by task. When the task is complete, they mark it with `Y` in the `Task Done` column.

At the bottom, in yellow, I want to calculate the percentage of tasks done separately by Sam and Heather, based on the amount of tasks assigned to them and whether or not they marked it with a `Y`. I tried `SUMIF` function, but I think I am barking up the wrong tree.

Is it possible to do what I want using Excel? If so, how?

-

Is there a column with the names? Assuming Sam/Heather in A2:A100 and Y if complete in B2:B100 then you can get the total tasks for Sam with

`=COUNTIF(A2:A100,"Sam")`

and the number of completed tasks for Sam with

`=SUMPRODUCT((A2:A100="Sam")*(B2:B100="Y"))`

so for a percentage completed just divide the latter by the former, i.e.

`=SUMPRODUCT((A2:A100="Sam")*(B2:B100="Y"))/COUNTIF(A2:A100,"Sam")`

....or an "array formula" can do that with a shorter formula, i.e.

`=AVERAGE(IF(A2:A100="Sam",IF(B2:B100="Y",1,0)))`

confirmed with CTRL+SHIFT+ENTER

`=arrayformula(AVERAGE(IF(A2:A13="Sam",IF(B2:B13="Y",1,0))))`
Hmmm, something is missing. I tried popping in the `SUMPRODUCT` formula, but all it says is: `error: Range has no entry corresponding to this cell`. The spreadsheet is editable, can you try it? – AngryHacker Jan 2 '13 at 0:47
Excellent, thank you. I did it the following way, which also worked: `=COUNTA( IFERROR( FILTER( A2:A12 ; A2:A12 = "Heather" ; B2:B12 = "Y" ) ) )`. It's a hack, since it's relying on an error to be generated, but works for now. – AngryHacker Jan 2 '13 at 1:28