Count Number of Open Items in the Last Month by Day

lbyn

I am trying to make a chart showing the number of open items each day in the last month. In the last month will be flexible so this will be a item that autopopulates the chart as the month goes on. I have a column for the formula =TODAY(-1), =TODAY(-2), etc. to populate the dates for the last 30 days. I have a "received" column to show when an item becomes 'open' and a "to client" column to show when an item is 'closed'. I am not sure what formulas to use to make this happen.

Paul Newcome

Try something along the lines of

 

=COUNTIFS(Status:Status, "Open", Received:Received, @cell = Date@row)

Eric M Oliveira

Hello,

 

Happy to help! If you'd like to count the number of open items in the last month Paul is correct you can achieve this utilizing a COUNTIFS Formula. The formula would be similar to the formula below. 

 

=COUNTIFS([Received Column Name]:[Received Column Name], "Open", [Date Column Name]:[Date Column Name], >=TODAY(-31))

 

Please replace the "Received Column Name" with the exact name of your column containing the "Open" value and replace the "Date Column Name" with the Date column that determines the rows finish date. I suggest in both cases copying and pasting the name of these columns directly from the column header name into the formula to ensure no misspelling. 

 

Note: If the value "Closed" isn't currently in the same column as "Open" you'll want to add this value to the "Open" column to ensure the formula functions properly. 

 

Have a wonderful day. Thank you for contacting Smartsheet Support.

 

Cheers,

Eric

Smartsheet Technical Support