
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 02, 2018 02:04 PM
I’ve read through all the Count-related posts and guides, as well as feature requests for COUNTIF, but am unable to solve a simple problem – and feel there must be a solution. So:
Table 1 (Projects) lists Projects in Column A.
Projects have several statuses (Active, Cancelled, Closed, etc.) in Column B.
Each Project has several People assigned to them in Column C.
Table 2 is linked to the People column in Table 1.
Column B in Table 2 Counts the number of Projects assigned to each person.
The Count however counts ALL projects, whatever their status.
How can I count, in Table 2 (Persons), just their ACTIVE Projects, from Table 1?
Any help would be much appreciated!
Javier

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 02, 2018 02:17 PM
There is a trick:
- Get a string with all the statuses of the projects, so:
Active,Closed,Active
and so on - Get the length of the string
- Replace all
Active
appearances by blank - Get the length
- The difference divided by 6 (the length of
Active
word), is the times that the word appears in the first string, so is the number of Active projects.
I think I saw it here: Count multiple keywords in text

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 03, 2018 09:48 AM
What I generally do is have a formula field (Say called Active) in Table 1 something like:
IF(Status = “Active”,1,0)
You can then use a rollup field in Table 2 to sum the Active field from Table 1 which will give you your answer.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 03, 2018 10:00 AM
Thanks Julian. Very helpful.
When I try to use that formula, it returns an error.
IF({Search Status} = “1. Active”,1,0)
Here, “Search Status” is the name of the column that indicates whether a project is active, cancelled, etc. The records I want to include have the value “1. Active” (vs. “2. Cancelled”, “3. Hold”, etc.).
Am I just not understanding this right?
Thanks!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 03, 2018 10:34 AM
Hi Javier
I’ve modified my field names to be the same as your’s and it’s still working - here are screenshots:
and the formula:
If this doesn’t help can you share your base (read only) or some screen shots?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 03, 2018 10:39 AM
I think the problem are the quotes. People copy from the forum instead of understanding and build the formulas themselves :roll_eyes:

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 03, 2018 11:31 AM
Works great! Thank you for that simple solution – and clearly explained.
All the best,
Javier

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 27, 2018 08:41 AM
Hi Elias,
Could you elaborate on this? What you’re saying looks really interesting, but I’m not following you on point 3. How would you replace all “active” appearances, and where would you replace them? In the statuses themselves, or would you use a formula in another field?
I’ve got a pretty technical problem that I am trying to wrap my head around but keep hitting dead ends. Any help you can provide will be amazing.
Kind regards,
Andrew

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 27, 2018 10:47 AM
Working from the same example @Elias_Gomez_Sainz was working with, you have:
- Project records with a “Status” field with the 3 Single Select options
- Another table, “People” that link to Projects
- A person may have many projects
- A rollup field in the “People” table that has
Projects
People
Rollup
Remove Active Projects
Compare Length of Strings and Divide by Length of the Word "Active"
Hide any fields you don’t need to see and show only the #Active
field.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 29, 2018 12:23 PM
It’s explained in the link I left.
