Save the date! Join us on October 16 for our Product Ops launch event. Register here.
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
Apr 02, 2018 02:17 PM
There is a trick:
Active,Closed,Active
and so onActive
appearances by blankActive
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
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.
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!
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?
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:
Apr 03, 2018 11:31 AM
Works great! Thank you for that simple solution – and clearly explained.
All the best,
Javier
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
Apr 27, 2018 10:47 AM
Working from the same example @Elias_Gomez_Sainz was working with, you have:
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.
Apr 29, 2018 12:23 PM
It’s explained in the link I left.