Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Counting Records that Meet a Criterion

10430 10
cancel
Showing results for 
Search instead for 
Did you mean: 
javieroneil
5 - Automation Enthusiast
5 - Automation Enthusiast

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

10 Replies 10

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

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.

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!

Hi Javier

I’ve modified my field names to be the same as your’s and it’s still working - here are screenshots:

Screen Shot 2018-04-03 at 18.30.17.png

and the formula:

Screen Shot 2018-04-03 at 18.30.35.png

If this doesn’t help can you share your base (read only) or some screen shots?

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

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

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

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
image.png

People
image.png

Rollup
image.png

Remove Active Projects
image.png

Compare Length of Strings and Divide by Length of the Word "Active"
image.png

Hide any fields you don’t need to see and show only the #Active field.

It’s explained in the link I left.