Cumulative count formula


#1

I’m new to airtable. Could someone help me on how to create a cumulative count, is it possible?

I schedule orders into batches. I need a sequential count of orders within each batch. The result would look like this:


#2

If I’m correctly understanding what you want to accomplish, in your case, I would suggest that you break up “orders” and “batches” into separate tables.

Then, using linked record fields, link each order number to its associated batch. On the batches table, you can use a count field to automatically count up the total number of orders.

Let me know if you have any questions!


#3

Thanks Katharine, yes I got as far as doing that…however what I wanted was to have a cumulative count within each batch. Eg order 1 of batch 1, order 2 of batch 1, order 3 of batch 1, order 1 of batch 2 , order 2 of batch 2 etc.

The reason I wanted it was to have a rollup that turned this into a mapping link for the batch in this format &loc1=[postcode-of-1st-order-in-batch]&loc2=[postcode-of-2nd-order-in-batch]&loc3=[postcode-of-3rd-order-in-batch].

However as I never have more than 100 in a route I got round round it by making it output &loc300=PO208DZ&loc300=PO203SJ&loc300=PO209EJ and then a nested substitution as shown below to replace the first 300 with 1, the second 300 with 2, etc etc. It was slow to set up but now works beautifully!!!

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Routing, “loc300”, “loc1”, 1), “loc300”, “loc2”, 1), “loc300”, “loc3”, 1), “loc300”, “loc4”, 1), “loc300”, “loc5”, 1)

Thanks Lyndon