Counting how many times a Single Select Field had hit a specific state?

[mocianto]

Hello guys! I am new to Airtable, and have been absolutely amazed by it’s possibilities. However, there is one specific thing where I got completely stuck for several hours and no YouTube videos or Forum topics helped me get through.

I am practicing on a table for a Car Dealership. Each record has 5 columns with Car Details, and then a Single Select Field where we can choose from “Available”, “Reserved” and “Sold”.

Then I have another table called “Car Dealership Statistics”, and here I would like to count how many times my cars went into “Reserved” and how many times they went into “Sold”. These numbers can be different because people can abandon their Reservations and the car becomes Available again.

I wanted to tackle this with a Counter column, a Formula column ( Counter+1 ) and an Automation that increases Counter by Formula when the field = Reserved, and then same when field = Sold.

However, I keep getting the message in my Automation that the Formula column can not be used to Dynamically increase Counter because it is computed. Any ideas?

[mocianto]

Hm, not sure if I fully understand the question. You can see on this screenshot, the Counter is set to 0 and the Formula is returning a 1. ( Counter+1 )

[Mohamed_Swella1]

Try doing the Trigger Test again and post a screenshot of the result please

[mocianto]

I just realised what the mistake was. I based my Trigger Condition on Status from the “Car Information” table, instead of the Lookup field in the “Car Dealership Statistics” table. Can’t believe I haven’t noticed for so long.

Thank you Mohamed!
Also thank you Scott and Alexey!

[Mohamed_Swella1]

I thought it might be something like that :slightly_smiling_face:

[mocianto]

Yeah, you suspected immediately. Spot on!

[ScottWorld]

That was going to be my next suggestion. Lol.