Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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

For Google docs try

=arrayformula(AVERAGE(IF(A2:A13="Sam",IF(B2:B13="Y",1,0))))

format formula cell as percentage

share|improve this answer
    
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
    
Oh, I see, It works perfectly fine in Excel, but not in Google Docs. I assumed that formula-wise at least they were equivalent. Will have to figure out how to do this in Google Docs. –  AngryHacker Jan 2 '13 at 0:50
    
In Google Docs try that last one with arratformula wrapped around it - I edited my answer to that effect –  barry houdini Jan 2 '13 at 1:16
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.