Formula or Automations usggestion for measuring unbroken and restarted streaks

Each month my Key Account Executives have to achieve a minimum metric of 10-4-4-2. In my column structure the 10 = “Initial MMS”, the first 4 = “Survey”, the secon 4 = “Final MMS”, and the 2 = “Qualified Win”

What I am needing help figuring out is how to capture individual months where they meet or exceed this minimum metric. Eaxample if a single person has 10 dates or more in the “Initial MMS” column, 4 dates more dates in the “Survey” column, 4 dates or more in the “Final MMS” column and 2 or more dates in the “Qualified Win” column that would constitute a streak of “1”.

So need to be able to break each person down by months YTD, and continuous unbroken streaks. Example if someone achieved 1,1,1,1,1 the streak would be 5. If it was 1,0,1,1,1,0,1 the streak would be 1. Anytime the streak is broken it starts over. Normally I would be looking at macros or vba in Excel to accomplish this or a smart index/match formula. How can I accomplish this in Monday?


monday formulas can only include values for a single item in calculations. One workaround for this is to use connect boards & mirror columns. For your situation, you might be able to create a summary board using this concept to get what you need.

But as you alluded to, you would most likely need additional processing capabilities to do it all. There are two apps in the monday marketplace that might help with the lookup functionality: “Match Index Column” and “VLOOKUP”.

Because monday provides users access to an API, you can utilize basically any web enabled automation/programming platform to read and update monday data. In fact, it is possible to use VBA to this. I have done this myself. But it is not for the faint of heart.

Two tools that monday recommends and many monday customers use are Zapier and Make (formerly Integromat). Both are considered “no-code”/“low-code” platforms. You can read more about the differences and why I usually recommend Make for more complex uses here: What is Make & How can it help you?

Jim - The Monday Man (YouTube Channel)
Watch Our Latest Video: Creating subitems using Make The Monday Man (EASY) Way
Schedule a 1-on-1 Tutorial Session

1 Like