Skip to main content

How to reproduce Excel's COUNTIF function and pivot table

  • January 29, 2016
  • 2 replies
  • 34 views
W

I am new to Airtable and still learning the basics. In Airtable I have a list of websites I manage, including a column for the webhost. My goal is to make a way to quickly see how many sites are on each host. When the list was in Excel, I used the COUNTIF:

=COUNTIF([Host],C3)

A               B              C
Domain          Host           Host Count
site1.com       hostA          2
site2.com       hostA          2
site3.com       hostB          1

I also made a pivot table that had in the first column a list of the unique entries in the host column in the main sheet, and in the second column the count

Host       Total
hostA      2
hostB      1

Is this possible in Airtable? Could someone point me in the right direction as to which features to use?

    2 replies

    M
    Forum|alt.badge.img+4
    • Mike_Eovino
    • Known Participant
    • February 7, 2016

    You should be able to get the second example to work, but you’ll need to use two tables (which is actually a good relational data model).

    1. Create two tables. Name one Sites and the other one Hosts.
    2. Add a Link to Another Record column to the Hosts table, name it “Sites” and link it to the Sites table. Be sure to Allow Linking to Multiple Records.
    3. Add a Count column to the Hosts table and set it to count the Sites table.
    4. Add some rows to each table and link them. Link multiple sites to at least one host to properly test the Count column.
    5. You should see the count of Sites associated with each Host in the Count column.
    A
    Forum|alt.badge.img+3
    • Adalberto_Santo
    • Participating Frequently
    • May 3, 2016

    I am trying to reproduce also the countif function but with 3 criteria, can some one help me come up with a formula to fill in my last column automatically. here is my table:

      Terms & ConditionsAccessibility statement