×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel 365 - How To Summarize Data From an Offset Column

Excel 365 - How To Summarize Data From an Offset Column

Excel 365 - How To Summarize Data From an Offset Column

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

CODE --> ExcelTable

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

RE: Excel 365 - How To Summarize Data From an Offset Column

Can you "get a total number of {Done} 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

RE: Excel 365 - How To Summarize Data From an Offset Column

Hi,

Consider putting your table summary ABOVE the Header row...
'
          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 
 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel 365 - How To Summarize Data From an Offset Column

Wouldn't that be:

          Tasks  Done
Fred          2     0
Jane          1     1
Matt          2     1
 
ponder

---- Andy

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

RE: Excel 365 - How To Summarize Data From an Offset Column

(OP)
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

RE: Excel 365 - How To Summarize Data From an Offset Column

@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 Ctrl+Shift+Enter.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close