Excel 365 - How To Summarize Data From an Offset Column 1

[MattGreer]

I have a table which consists of a list of to do items. The table is set up like this:

Assignment | Who   | Status |
----------- ------- --------
Task 1       Matt    Done
Task 2       Matt
Task 3       Fred
Task 4       Jane    Done
Task 5       Fred

What I want to be able to do is have a summary table somewhere that shows the total number of Tasks assigned to each person and their respective % complete. When something is complete, the word 'Done' will appear in the third table column.

I'm able to get a total number of Tasks for each person using the COUNTIF function based on their name in the 2nd column of the table, but what I don't know how to do is how to perform a check on each one of a person's tasks to see if the cell has the word 'Done' in it or not.

Thank you for your help!

Thanks!!


Matt

 

[Andrzejek]

Can you "get a total number of [blue]{Done}[/blue] Tasks for each person using the COUNTIF function based on their name in the 2nd column of the table"?

This way Matt has 2 tasks, 1 is Done, so he is 50% complete
Fred has 2 tasks, none are Done, so he is 0% complete
Jane has one task, one Done, so she is 100% complete

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

Hi,

Consider putting your table summary ABOVE the Header row...
[pre]
'
Tasks Done
Fred 2 0%
Jane 1 100%
Matt 2 50%
Assignment Who Status
Task 1 Matt Done
Task 2 Matt
Task 3 Fred
Task 4 Jane Done
Task 5 Fred
[/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Andrzejek]

Wouldn't that be:
[pre]
Tasks Done
Fred 2 0
Jane 1 1
Matt 2 1
[/pre]


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

Yup

I edited it and made the %

@Matt, hope you're using Structured Tables. Makes this sort of summarization sooo much simpler.
[URL unfurl="true"]https://res.cloudinary.com/engineering-com/raw/upload/v1626737556/tips/tt-MattGreer_TableSummary_mnnsiy.xlsx[/url]
Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[MattGreer]

Andrezejek,

To answer your first post, yes, I was already using COUNTIF to total up the number of times each person's name appeared in the table. But if I did COUNTIF on the third column, I'd just get the total tasks that have been completed, and not the ones specific to a person. But perhaps I didn't understand your comment fully!

Skip,

Initially the table was structured but Excel won't let me set it up as a shared workbook with that kind of formatting. I want it to be shared as there are multiple users. But that's not that important right now. It's very unlikely more than one person is going to use it at a time and even then, the 2nd person can wait!

I downloaded your example and... wow... I've never seen formulas formatted in that manner. I get to learn something new and that's AWESOME. I still have no idea how you acquired so much knowledge. You should write a book. Thank you!!

Thanks!!


Matt

 

[SkipVought]

@Matt, I learned most of what I know about Excel from others here at Tek-Tips over the past 2 decades. I saw features and techniques used by others that I believed would be useful tools in my trade of data acquisition, analysis and manipulation.

Those formulas in the example workbook, can be used with or without Structured Tables, but the references would need to use A1 or R1C1 notation, not as elegant or understandable as the relevant names that can and ought to used to identify Tables and Fields.

If you are using an earlier version of Excel, you might need to ENTER your SUM Array formulas using [tt]Ctrl+Shift+Enter[/tt].

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein