Welcome!

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,

Find more posts tagged with

Comments

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)

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

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

=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")

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

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

=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?

Sign in to join the conversation!

Quick Links

Trending discussions

Delete
How can I delete old sheets I no longer use from my account?
How do I get a dashboard chart based on a report to display as a donut chart?
I'm trying to display a task progress chart showing "complete", "late", and "not complete" in my dashboard and for some reason the chart will display as a column chart but it won't display as a donut chart even though I'm referencing the same report. I'd prefer to display as a donut chart as I think it looks better and is…
Keep getting Selected data cannot be charted. Please select a new range.
Hello, I am trying to create a chart widget in my dashboard, but I keep getting "Selected data cannot be charted. Please select a new range." error. The report I am using is already summarized in numeric value. What is funny is I have report that I created a chart widget in another dashboard, and when I use that same…