Counting Records that Meet a Criterion

[javieroneil]

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

[Elias_Gomez_Sai]

There is a trick:

1. Get a string with all the statuses of the projects, so: Active,Closed,Active and so on
2. Get the length of the string
3. Replace all Active appearances by blank
4. Get the length
5. 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

[Julian_Kirkness]

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.

[javieroneil]

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!

[Julian_Kirkness]

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?

[Elias_Gomez_Sai]

I think the problem are the quotes. People copy from the forum instead of understanding and build the formulas themselves :roll_eyes:

[javieroneil]

Works great! Thank you for that simple solution – and clearly explained.
All the best,
Javier

[Andrew_Brown1]

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

[Jeremy_Oglesby]

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.

[Elias_Gomez_Sai]

It’s explained in the link I left.