Formula to Count Ages of Orders

Galadriel975

I'm trying to create a formula that counts the open orders based on the date the order was created. The thing complicating the formula is that it's for a metrics dashboard, so I need it to count the orders that are between 1-3 months old, 3-6 months old, 6-9 months old, 9-12 months old and over a year. I'm at a complete loss as to where to even start with the formula unfortunately. I know that it'll need to use the COUNTIFS function, as well as the TODAY function, but I'm not sure if I'll need a helper column to make it work. Any help to get this started would be appreciated.

Kelly Moore

Hey @Galadriel975

You will need a field in your main sheet to calculate how much time, per row, has passed. If you don't have a column for this then yes, you need this helper column. I also assumed you had a Status or Stage column that indicated your order was Open. I called it Status - change my Status reference to the name of your actual column. The same goes for the Date column- change the name to your actual column name.

I did add one more category - 'greater than 12 mos'. Maybe you never have orders that are that old, but if you ever do, it will cause an error in the formula. My addition corrects for that.

=IF(Status@row = "Open", IF(ISDATE([Order Created]@row), IF(TODAY() - [Order Created]@row <= 90, "1-3 months", IF(TODAY() - [Order Created]@row <= 180, "3-6 months", IF(TODAY() - [Order Created]@row <= 270, "6-9 months", IF(TODAY() - [Order Created]@row <= 360, "9-12 months", IF(TODAY() - [Order Created]@row > 360, "over 12 mos")))))))

How is your metrics sheet set up? Can you share a screenshot? Specifically I'm looking to see if you have the names of the month categories as rows, or if you have a column designated for each category. The setup impacts the COUNTIFS you would use on that sheet. As an alternative to a metrics sheet, could you use a report to show totals? Although you can't bring the data into a metric widget, you can default the report to a collapsed view on a dashboard and size your widget accordingly. A report is the simplest way to produce your data and keep it evergreen without ever touching it again.

@mention me when you add the screenshot and/or reply to my questions and I can help you with the COUNTIFS, if help is needed.

Will the above work for you?
Kelly