How to create a formula that uses record counts?

Hi ​@blmahonsmsl,

Welcome to Airtable. You can certainly achieve this.  I'll show you two ways to do this, which will demonstrate how to use Count fields and Rollup fields and the difference between them.

1. Via Count Field
   Add a Count field to your Page Views table. In the "Select count source" field, select your field that links to your Registrants table. This will return the number of registrants for that date (we can call this field Count Registrants). Next, create a formula field (Conversion Rate). Here, you can enter a simple formula to give your conversion rate: {Count Registrants}/{Page Views}

2. Via Rollup Field
   You can achieve the above with a single Rollup field, which works like a Count field, but lets you perform calculations on your data. Create a Rollup field and once again select your Registrants linking field in the "Select rollup source". Rather than returning a count of your registrants, this field will allow you to perform direct calculations on the data returned from your Registrants table. You therefore won't need a Count Registrants field. Instead, enter the following formula - COUNTALL(values)/{Page Views} to get your Conversion rate.

The above assumes your tables are linked in a way that allows your Page Views table to "see" all registrants for that date. How have you linked them? Is it via the date field?

Tim
minnnow.tech

I don’t know how to link them or know what field it is best to link them with.

This first screenshot is the columns i have in the main table that has all of the registrants.

And this is my page views tab.

If you can point me in the direction of how to link these two so that i can try your formula, that would be awesome.  Thanks for your assistance.

No worries mate.

You can use the Date Registered field in your Registrants table to link to your Page Views table. To do this, I recommend duplicating your Date Registered field and then converting it to a linked record to preserve your data. Here is a walkthrough of how to do it, which also shows you how to create your formula using option 1 above. Ignore option 2, as I was talk out of my hat about how Rollup fields work. 

Your options for getting data into your new linked field will depend on how your records are created.  Is that done manually by yourself or is there a form or perhaps an automation creating records in your tables?

Tim
minnow.tech

Hey ​@blmahonsmsl!

1. You’ll need a linked field between your two tables. More on linked fields here.
2. You’ll need a count field on your Page Views table. More on count fields here.
3. You’ll need a formula -on your Page Views table- which will be:

{Registrants Counts}/{Page Vies}

Make sure to replace the name of the fields on the formula with your actual field names.

Please let us know if you need any further help!

Mike, Consultant @ Automatic Nation

Assuming you’re doing the linking manually after you’ve imported your data, you could try:

1. Create a formula field called ‘Date Formatted’ in both tables, and make them output the date as ‘DD MMM YYYY’
2. Make the primary field of ‘Page Views’ be the ‘Date Formatted’ field
3. In ‘Registrations’, click the header of the ‘Date Formatted’ field, hit copy, then paste it into the linked field to ‘Page views’
   1. This will automatically link the data together

   2. 

4. In ‘Page Views’, create a ‘Count’ type field and point it at the linked field

• Screenshots:
  • 

  • 

Thank you kindly for that loom video.  Very helpful.  Now I would like to separate my registrants from those that came organically vs fbad so I can calculate conversion rates for them separately.  Here is a loom showing you what i mean.  If you or anyone else has an idea, I would greatly appreciate it.

https://www.loom.com/share/819852f566d042f3a511cdaa0002b0dc?sid=cc68c139-13e3-48b9-8187-c8ad6db721be

You can calculate the registration conversion rate by using a Rollup field and a Formula field. Here’s how:

1. Link the Tables:

   • In the "Page Views" table, add a Link to Another Record field connecting it to the "Registrants" table.

   • Link each page view record to the corresponding event registration date.

2. Rollup Field for Registrations:

   • In the "Page Views" table, create a Rollup field on the linked "Registrants" table.

   • Use COUNTALL(values) to count the number of registrations per day.

3. Formula for Conversion Rate:

   • Add a Formula field in the "Page Views" table:

     nginx

     CopyEdit

     {Total Registrations} / {Page Views}

   • Format it as a percentage.

If you need help setting this up, feel free to reach out!