counting "assign to" or individuals

onurerol

Hi All,

I want to take numeric data for dashboard and need to count the "assign to" / individuals on tasks in another project or sheet.

I use the below formula types;

=COUNTIF({ Other project Range 1}, FIND("individual 1", @cell, 0))

=COUNTIF({other project Range 1}, "individual 1")

However, when I select the correct column and write the correct name, it gives me 0 as a result. How can I fix it?

I need numbers like this person has that number of tasks and also ... number of tasks completed / in progress.

Thank you,

 

Paul Newcome

Using your first formula, you will need to finish out the criteria. FIND is not a true/false function. It returns a numeric value based on where within the search field the text specified was found (includes spaces). If it doesn't matter where the specific text is within a cell just that it is there somewhere, you can use

 

=COUNTIF({Other project Range 1}, FIND("individual 1", @cell, 0) > 0)

 

To help get a feel for the FIND function. Use a few sample cells and then use only the FIND function on them. In the table below you will see the sample text and the results of the following FIND function (column headers in bold).

 

=FIND("a", Sample@row)

 

Sample               FIND

abbbbb                   1

bbbabb                   4

bbabbb                   3

bbbbbb                   0

.

You will see that it tell you what position the letter "a" is in within the string. When looking for a name, it will return the value of the location of the first letter in the string.

 

=FIND("Paul", Sample@row)

 

Sample               FIND

Paul                       1

a Paul                    3

aPaul                     2

John                      0

.

So to circle back to your original formula, to denote that the name was found anywhere within the list of names, you will need to specify that the result of the FIND function is greater than 0.

Paul Newcome

In regards to your second portion of counting tasks that are Completed, Not Started, or In Progress, you would change to a COUNTIFS formula, use your original requirements from the first, but then add in your extra requirements.

 

=COUNTIFS({Other Project Range 1}, FIND("Name", @cell) > 0, {Other Project Range Status Column}, @cell = "In Progress")

onurerol

Hi All,

Another comment; when I try to count the individuals in other project sheet etc, I receive a warning like "please select a cell or range of cells that are next to each other. If your rows are collapsed, please expand them before selecting".

However, I dont receive a warning like the above when I try a different sheet that is not usable for me or a dummy sheet created to test by myself.

Thank you,

onurerol

Thanks, let me try these ones

onurerol

Hi Again,

I think I got the problem with the formula. My goal is to count the names in a column. For example;

Tasks              Assign to                                 Status

Task 1             Paul.x@...com                        complete

Task 2             Jeff.x@...com                         incomplete

Task 3             Paul.x@..com                           complete

Task 4             John.x@...com                        complete

We have emails on assign to column (it's status is "contacts" shared with other colleagues)

We want to count Pauls to see how many tasks on him and how many is completed or incomplete.

For example; on the above example we have 2 Pauls as complete. So, I can see Paul had 2 tasks and these are completed on my dashboard. (I wanna reflect these on a dashboard)

And we have 1 John which is complete. So, I can say he had one task and it is complete. So, I can assign one task to John to arrange task load and work load.

There are many project sheets like that and I will gather "assign to" sections on one Grid and reflect the numeric data to a dashboard.

Do you have any idea to achieve this? 

Thank you,

Paul Newcome

Would you be able to post screenshots of the ranges you are trying to select? It would be easier to figure out exactly what is causing the errors.

Paul Newcome

Let me make sure I have this correct. You have multiple sheets all set up as in your example above? You want to use a separate sheet to count across ALL project sheets how many tasks each person has completed to be displayed on a dashboard?

onurerol

yes correct. We have many clients / projects and each project has separate project sheet. There are many "assigned to" individuals in these sheets with the tasks like the above. 

 

Mike Wilday

Paul, nice walkthrough! 

Are you trying to get the number of incomplete and complete projects in the same cell? 

If so, you can join two formulas... =Countifs(criterion 1 range, criterion 1, Criterion 2 range, criterion 2), +" Incomplete &" + =Countifs(criterion 1 range, criterion 1, Criterion 2 range, criterion 2), + " Complete"

Paul Newcome

Thanks Mike! 

Paul Newcome

Ok. I would suggest setting up your metrics sheet as below (column headers in bold):

Overall Totals:

Name         Email               Project 1     Project 2     Project 3     Total

Paul            paul@email          f1                f2                 f3              f4

John            john@email         f1                f2                 f3              f4

Mike            mike@email         f1                f2                 f3              f4

.

f1:

=COUNTIFS({Project 1 Assigned to column}, FIND(Email@row, @cell) > 0)

.

f2

=COUNTIFS({Project 2 Assigned to column}, FIND(Email@row, @cell) > 0)

.

f3

=COUNTIFS({Project 3 Assigned to column}, FIND(Email@row, @cell) > 0)

.

f4

=SUM([Project 1]@row:[Project 3]@row)

.

.

Completed Totals:

Name         Email               Project 1 C     Project 2 C     Project 3 C     Total C

Paul            paul@email            f5                      f6                     f7                f8

John            john@email           f5                      f6                     f7                f8

Mike            mike@email           f5                      f6                     f7                f8

.

f5:

=COUNTIFS({Project 1 Assigned to column}, FIND(Email@row, @cell) > 0, {Project 1 Status column}, @cell = "Completed")

.

f6

=COUNTIFS({Project 2 Assigned to column}, FIND(Email@row, @cell) > 0, {Project 2 Status column}, @cell = "Completed")

.

f7

=COUNTIFS({Project 3 Assigned to column}, FIND(Email@row, @cell) > 0, {Project 3 Status column}, @cell = "Completed")

.

f8

=SUM([Project 1 C]@row:[Project 3 C]@row)

.

.

If you put these formulas in the top row using the appropriate steps for cross sheet references, you should be able to dragfill these on down.

To display in a Dashboard, I would need to know how exactly you want the data displayed. Just raw data (name and total), chart, etc?

Cindi Meche

Hi - Jumping in on this older post hoping I have a similar issue. For some reason, my formula returns 0 even though I have 2 "Sara"s.

=COUNTIF({Other project Range 2}, FIND("Sara", @cell) > 0)

What could be missing?