Current year streak and rolling unbroken streak formula help

Adam Kinney

I have a dashboard metric that I could use help with. I have a Consistency Club to recognize associates that achieve their monthly sales activity metrics, measured by individual month. Meaning if they have achieved their metrics for Jan, Feb, Mar, Apr, May and June their current year consistency streak would be 6. If the prior year they achieved 12-of-12 months the current unbroken streak would be 18. The example I described is displayed on the dashboard like below:

The data comes from a metrics sheet which utilizes a COUNTIF formula to read the individuals call report measuring their activity.

=COUNTIF({Initial Pres}, 1)

Our metric "10-4-4-2" represents distinct sales steps indicated by date in individual columns on their call report and displays like below on the metrics sheet:

This only captures the current month.

I need a way to measure and track each individual months metrics and if they are achieved 100% each month or not, then need the formula or process to measure the current year unbroken streak AND measure the overall unbroken streak that may span several months/year.

Meaning in 2021: If they achieved 100% every month through August the current year streak would be 8. If they had achieved 100% in Jan, Feb, Mar, missed in April, and then 100% again in May, June, July and August their current year unbroken streak would be 4.

For the overall rolling unbroken streak it would be the same logic going back infinitely on their call report if unbroken and each individual month was 100% every time.

Call report columns where they enter dates for the 10-4-4- portion of the 10-4-4-2 metric looks like this:

The 2 of the 10-4-4-2 comes from a CRM uploaded sheet via Data Shuttle (listed in separate images to span all columns:

=COUNTIFS({Qualified}, "Yes", {Open Month}, [Column2]2, {KAE}, [Column2]1)

Column names in formula:

Qualified = [Qualified Key Account]

Open Month = [Key Account Open Date]

{KAE} = Name in cell on metric sheet [Column2]1

Rough image to further demonstrate need:

How can I utilize a formula or sheet additions to make this happen?

Genevieve P.

Hi @Adam Kinney

I believe I figured out a way to make this work, however it wouldn't store the historical data in the same way your final image is showing. This is how I've set up the sheet that would be used for the Dashboard:

The Date column is set to be a date type of column, identifying the 1st of each month.

The Year column is a simple column formula of =YEAR(Date@row)

The Checkbox column would house a cross-sheet formula to find out if the goal was met for that month or not. Note that if the Date is in the future, we'll want to return some sort of text (such as the "-" in the image above) so it will be ignored from the formulas and not seen as blank.

Then we have the two formulas: the Current Year Streak and the Rolling Unbroken Streak.

I have these two formulas put in the Sheet Summary section, as they will update as soon as the data in the sheet updates, so they really only need one cell to house the number versus an entire column.

Here's the two formulas I used... I'll start with the easier one:

Rolling Unbroken Streak Formula

=IF(COUNTIFS(Checkbox:Checkbox, 0, Date:Date, <>"") = 0, COUNTIF(Checkbox:Checkbox, 1), COUNTIFS(Checkbox:Checkbox, 1, Date:Date, >(MAX(COLLECT(Date:Date, Checkbox:Checkbox, 0)))))

First it checks to see if there are any unchecked boxes (on the off-chance that someone has been on a streak forever!) and if there aren't, then it simply counts the number of checked boxes.

However, if there's an unchecked box, then it looks to Count only the rows that are GREATER than the MAX Date that has a 0.

We can now use the same logic for your Current Year Streak, but instead of just looking for the MAX date, we also need to add in parameters that look for Today's Year.

Current Year Streak Formula

=IF(COUNTIFS(Checkbox:Checkbox, 0, Date:Date, <=DATE(YEAR(TODAY()), 12, 31), Date:Date, >=DATE(YEAR(TODAY()), 1, 1)) = 0, COUNTIFS(Checkbox:Checkbox, 1, Date:Date, <=DATE(YEAR(TODAY()), 12, 31), Date:Date, >=DATE(YEAR(TODAY()), 1, 1)), COUNTIFS(Checkbox:Checkbox, 1, Date:Date, >(MAX(COLLECT(Date:Date, Year:Year, YEAR(TODAY()), Checkbox:Checkbox, 0))), Date:Date, <=DATE(YEAR(TODAY()), 12, 31)))

So, this does the same thing. It checks to see if there are any 0's for this current year so far, and if there aren't, then it returns the total number of checked boxes for this year.

But if there's at least one 0, then it finds the row with the MAX date with that 0, and only counts the checkboxes after that date.

Does this make sense/will it work for you? If you need help with the cross-sheet formula creating the checkboxes, I'm happy to think on this further. I'd likely need to know the criteria for whether or not you're at 100% though, if you wouldn't mind clarifying!

Cheers,

Genevieve