Counting a column and then putting in a single cell

There is a way to accomplish what you want, but it’s a bit of a hack and will require manual maintenance after the initial setup.* Otherwise, you might want to consider using record grouping to sum the number of leads per month.

1. In the table with your list of leads (let’s call it [LEADS]), create a formula field {Month} that contains the following:
   =DATETIME_FORMAT(CREATED_TIME(),‘YYYY-MM’)
2. Group based on {Month}. The header of each group should show you the number of records it contains.

The way to get what you want is to do the above, but then
3. Convert {Month} from a formula field to a link to another table (this means that new records won’t automatically be tagged with their month of creation, because there will be no formula that automatically calculates it)*, which will have records “2017-01”, “2017-02”… This may be your existing dashboard table, or a new one, depending on how your current dashboard is structured, but let’s call it [MONTHS].
4. Create a rollup field in [MONTHS] that counts the number of entries in the linked field from [LEADS].

*Note you can have records automatically get a month assignment through an AirTable quirk with grouped records. If you group the records in [LEADS] by {Month}, any new record in {Month} will inherit the grouped field value. That is, if you create a new record in the group “2017-07”, it will automatically have “2017-07” set. So the only time when you’ll have to manually change the {Month} value is at the start of a new month.

Hi @Andy_Lin1 – thanks for this. This is a great solution indeed, although it will require more work than I currently do to update the list, so I’ll leave it for now.

I think the functionality I’m looking for is essentially what is normal formula on Excel, but currently not supported in AirTable. I’ll do manual for now :slightly_smiling_face:

Thanks again so much!

If you have a datetime field, you can have another field display the month of the datetime with Month([datetime])

Hi @Thomas_Jaccino, yes I’ve done that before – my issue is then getting a count of all of those fields into another table.

There is a way to do this – but it is a bit non-intuitive. It also requires you to link each new Contact record to a ‘ContactsByMonth’ table – a bit of manual effort for which I’ve yet to identify a work-around. (This applies to new contacts only; existing ones can be updated en masse.) The example base linked at the end of this reply offers a solution assuming a maximum of 99 contacts tracked per month but can be modified to support larger maximums.

I’ll start with the non-scary part, first: the finished product. In this demo, I started with 200 dummy contact records, each with a fake CREATED_DATE in 2016 or 2017, in the ‘Contacts’ table. In the ‘ContactsByMonths’ table, the ‘ContactsYear’ field contains a string showing per-month contacts for that year, as so:

(I wasn’t sure how you planned to display contacts on your dashboard page; you could as easily show a rolling month, this month/last month, or any other temporal grouping you wish.)

Now for the ugly details. The solution is based on the technique I described here; in effect, what it does is use successive pairs of digits in a large integer to track contacts per month:

Ja.Fe.Ma.Ap.Ma.Ju.Jl.Au.Se.Oc.No.De
00.00.00.00.00.00.00.00.00.00.00.00

(Unfortunately, this runs up against Airtable’s degree of precision, so I track January-June and July-December independently.)

If you look at the Contacts table, you’ll see a column of faked (seeing how I have no access to a time machine) CREATED_DATEs; from there, I extract the month into ‘GetMonth’. The real work is done in the next step:

IF(GetMonth<7,1*POWER(10,(GetMonth-1)*2))

(That handles January through June; for July through December, the formula is

IF(GetMonth>7,1*POWER(10,(GetMonth-7)*2)) .)

Finally, ‘YearToCount’ links to the ContactsPerMonth table.

What happens there depends on what you want to do with the data. Since I intended to display monthly counts as part of a string, I first converted the encoded monthly rollups into strings and then used LEFT() and MID() to extract individual months. Alternatively, if I needed a numeric value per month, I could have used

INT({EncodedCounts}/[MonthOffset])

where [MonthOffset] = 10,000,000,000 for the left-most encoded month, and

INT(({EncodedCounts} - [PreviousMonthCount*PreviousMonthOffset]/[CurrentMonthOffset])

for subsequent months. (Or alternatively alternatively, I could have used VALUE({MonthStr}); there are multiple paths to the same end point.)

As I said, it’s not particularly pretty – but it gets the job done.

=============

To use existing Contact records, first create the ContactsPerMonth table. Create a row for each year you wish to track. In the Contact table, create a new field with the formula

YEAR(CREATED_DATE)

Using ‘Customize field now’, change the field type to ‘Single line text’. Next, change the field type to ‘Link to another record’, and specify ‘ContactsPerMonth’ as the table to which to link. This will correctly load all the links.

Hey @W_Vann_Hall, this is awesome – thank you so much for taking the time to explain this to me.

I don’t fully understand it I must say, but I will play around it with it and try to understand it!!!

The end result of this process that you’ve created, as I see it, is that on the ContactsPerMonth table, you get a column called YearContacts that lists the number of people that have been contacted per month.

It’s darn impressive. I’ll have to see how it can fit into what I want to do.

Heh… I think I mentioned this was based on work I’d originally done in preparation for this post. When I first went back to it while writing my reply, I couldn’t figure out what the heck it did, either. :winking_face:

LOL – I’ve read both posts, and think I get it now.

So the manual part from what I can see, on a per record basis, is that you manually select a year for YearToCount, right?

Right – or whatever unifying feature you desire. Essentially, all Contacts you wish to tally must be linked to a single record in the table where you wish the tally to be performed.

So if I want a table that has a line per month, with various measurements, I want the link to be month, and possibly month + year

Right?

Hi Scott

To give you yet another option, I think this could be done by using Zapier (you would need a paid account) which would trigger each time you enter a new CRM lead record and then add one to the value of a counter in your dashboard table for the relevant month. The basic logic of the Zap would be something like:

1. Triggered by the new CRM record
2. Find a dashboard record with the relevant year and month (if not found create it)
3. Add one to the value in the lead count field and update it.

This will work fine as long as you realise that it won’t be able to reduce the count if you delete a record (for example).

I have done quite a bit of work for clients automating Airtable with Zapier and find it all works well as long as you understand the limitations (Zaps don’t happen immediately, can only happen once per record at the moment and can only be triggered by New Records in a Table or new records in a view).

Hi @Julian_Kirkness, thanks for this – very useful to be reminded.

Personally I want an automated version that doesn’t require third party, as I know the pain of Zapier changing something!

I think, however, thanks to @W_Vann_Hall, I’ve found a solution. Seems to work at the moment, and with minimal effort on my part now that I’ve built it.

:slightly_smiling_face:

Correct. Basically where @Andy_Lin1 was coming from.

Only external solutions exist for that part I’m afraid…

Yes I see that now! Thanks @Andy_Lin1 :slightly_smiling_face: