Filtering a Count field

[Win_Iden]

Is it possible to filter the results that are counted in a Count field? Example: “Tasks” table is a list of tasks, with a single select column ‘Stage’ that has values representing what stage of completion a Task is in and a ‘Facilities’ column representing buildings. A second table, “Facilities”, is linked and I want to count the number of tasks for each facility but exclude anything with a ‘Stage’ value of Complete.

[Tuur]

You can add an additional formula field that is filled only when the stage is not equal to ‘Complete’. Then use that field for counting in your other table…

[Luke_Waring]

@Tuur thanks for this idea. Can you please explain in a little more detail how to do this? I’m trying to use the COUNT function to create a ranking of banks advising on tech IPOs. I’d like to filter out deals involving companies headquartered in China because they are skewing my results. I turned on this filter, but it doesn’t seem to affect the table with my count column. Here are two screenshots:

[Luke_Waring]

2nd screenshot. These numbers include the deals for China-based companies, which is what I’m trying to cut.

[Jeremy_Oglesby]

In your table called “Top Lead Left Banks ex China”, you need to create a “Lookup” field that looks up the Issuer(2018 U.S. Tech IPOs) => “Headquarters”. Then, you can filter on that field in your “Top Lead Left Banks ex China” table, as you are wanting to.

Essentially, when you link records across tables, you can also pull information about those linked records from one table and into another via Lookups or Rollups.

Airtable - Support

Guide to formula, lookup, count, and rollup fields

Formula, lookup, count, and rollup fields (columns) are powerful field types that allow you to compute a value based on values in other cells. These fields are configured in the field configuration...

[Luke_Waring]

Thank you @Jeremy_Oglesby! This works to cut every bank that worked on any China-based deals. Do you know of a method to keep the banks that worked on China-based deals, but drop only those specific, China deals from the count?

I’ve included a screenshot showing what happens when I filter for “is not China” or “does not contain China.”

Maybe there’s some kind of conditional statement / formula I could use? Apologies if I’m missing something simple. And thank you! I’m new to Airtable but am finding the community and product to be exceptional.

[Jeremy_Oglesby]

If it’s only dropping the China based deals from the IPO count that you are interested in, you could do this:

• Create a formula field in your “2018 U.S. Tech IPOs” table called “Non-China” with this formula:

IF(
   FIND(
      "China",
      {Headquarters}
   ),
   0,
   1
)

Each Issuer that has a headquarter in China will be assigned a value of 0, and all others a value of 1.

• Then rollup that field in your “Top Lead Left Banks ex China” in a rollup field called “Non-China IPOs”
• You won’t be able to filter out only the China Issuers from the “Issuers” field, but you can put the “Non-China IPOs” field right next to the “Number of IPOs” field to see the difference and spot those that have one or more China IPOs.
• Then, if you want, you could create a formula field called “China IPOs” that is just:

{Number of IPOs} - {Non-China IPOs}

• Use that field to Group your results (next to the Filter menu) by how many “China IPOs” they have to make it easier to scan them that way
• You could also do a formula field to find “% China IPOs” and Group by that so that Banks w/ 5/20 China IPO’s end up in the same group with Banks that have 1/4 China IPO’s