Count Number of Open Items in the Last Month by Day

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.

Find more posts tagged with

Construction

Formulas

Project Management

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Dropdown list restrictions
Is there a way to limit a dropdown list so that the values in the dropdown list can only be used once in a column?
seeking VLOOKUP or equivalent, search for most recent value, multiple dropdowns
My product/use case: I seek to build a camera inventory. Each camera has a unique identifier. Cameras are mobilized to different locations on different days. I need help making a VLOOKUP or equivalent combination of formulae functions to run 2 queries cross-referencing 2 grids, searching a column with multiple dropdown…
Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet