counting "assign to" or individuals
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,
Comments
-
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.
-
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")
-
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,
-
Thanks, let me try these ones
-
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,
-
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.
-
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?
-
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.
-
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"
-
-
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?
-
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?
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives