Monthly Turnover Time

Hello, I am working to create a widget on a dashboard. In order to do this, I want to get data between a start date and end date (turnover time) per month to show monthly progress. I have tried by finding the month and year but I'm not sure how to attach the turnover time days with its corresponding month/year

=COUNTIFS([Month of Creation Dates]1:[Month of Creation Dates]244, =1, [Year of Creation Dates]1:[Year of Creation Dates]244, =2018)

I then tried to find the sum but it gives a massive number no where close to what I'm looking for (I also realize this next formula has no year aspect but I'm not sure how to incorporate it quite yet)

=SUMIF([Month of Creation Dates]1:[Month of Creation Dates]244, [Month of Creation Dates]1:[Month of Creation Dates]244 = 1, [Turnover Time]1:[Turnover Time]244)

Any suggestions/solutions appreciated!

Thank you for your time.

Find more posts tagged with

Manufacturing

Formulas

Services

Marketing

Support

Solution Center

IT & Operations

Project Management

Cell linking

Comments

Paul Newcome

What type of widget are you wanting to use and how would you want the data displayed within the widget?

cjleon

I am wanting to use a chart widget to display a monthly average of turnover time. A bar chart would be sufficient.

Paul Newcome

Are you able to provide some screenshots of your sheet(s)?

cjleon

Sorry for the late response! Didn't get a notification.

The formula I have now is manual but I was looking for something more automated where I don't have to put the cell name in order for it to be counted.

turnover time months.JPG

turnover time formula.JPG

Paul Newcome

Long story short, you are going to want to use and AVG(COLLECT( formula. The first range is the range of numbers to be averaged. You can then specify criteria range/criteria within the rest of the COLLECT function.

Long story long...

See the below screenshot. It may have a little more too it than what you are looking for, but it will cover pulling at the very least numbers from a specific month and/or year and can be adjusted in many different ways to include numerous amounts of criteria depending on how specific of a result you are looking for.

Basically what I do is create a table. I name the first column as "Year" and then every column after that is the corresponding month name from January on to December.

I then list the year I am wanting to pull for in row 1 of the year column and then continue across row one with the month numbers 1 - 12.

If I have multiple sets of data to compare against the same criteria, I list those down the Year column starting in row 2.

I then use the following:

=AVG(COLLECT($Count:$Count

The name of the column where the numbers I want to average is located.

=AVG(COLLECT($Count:$Count, $Count:$Count, ISNUMBER(@cell),

Will skip over any cells in the Count column that does not contain a number (gets rid of the #INVALID DATA error).

=AVG(COLLECT($Count:$Count, $Count:$Count, ISNUMBER(@cell), Date:Date

My Date column I am using to search against.

=AVG(COLLECT($Count:$Count, $Count:$Count, ISNUMBER(@cell), Date:Date, AND(

I am looking at multiple criteria for the same range (month and year), so I specify AND to keep from having to duplicate the range entry.

=AVG(COLLECT($Count:$Count, $Count:$Count, ISNUMBER(@cell), Date:Date, AND(YEAR(IFERROR(@cell, 0))

Tells the formula to look at the year, but if there is no year to use 0. This will put a 0 in place of the year if the date cell is blank so it will essentially skip over blank date cells without throwing an error on the YEAR function.

=AVG(COLLECT($Count:$Count, $Count:$Count, ISNUMBER(@cell), Date:Date, AND(YEAR(IFERROR(@cell, 0)) = $Year$1

Refers to the cell where I entered my year in the table.

=AVG(COLLECT($Count:$Count, $Count:$Count, ISNUMBER(@cell), Date:Date, AND(YEAR(IFERROR(@cell, 0)) = $Year$1, MONTH(IFERROR(@cell, 0))

Same as the YEAR but looking at the month instead.

=AVG(COLLECT($Count:$Count, $Count:$Count, ISNUMBER(@cell), Date:Date, AND(YEAR(IFERROR(@cell, 0)) = $Year$1, MONTH(IFERROR(@cell, 0)) = January$1)

Refers to row 1 of whatever column the formula is in (note the $'s used throughout to lock references for drag-filling).

=AVG(COLLECT($Count:$Count, $Count:$Count, ISNUMBER(@cell), Date:Date, AND(YEAR(IFERROR(@cell, 0)) = $Year$1, MONTH(IFERROR(@cell, 0)) = January$1)))

Closing out the AVG and COLLECT functions using )) then drag-filling on down the row will give you your monthly averages.

=AVG(COLLECT($Count:$Count, $Count:$Count, ISNUMBER(@cell), Date:Date, AND(YEAR(IFERROR(@cell, 0)) = $Year$1, MONTH(IFERROR(@cell, 0)) = January$1), $Type:$Type, @cell = $Year@row))

For the example in the screenshot I also broke it down by type, but pulling for year and month alone is detailed in the previous steps above.

Does this help any?

Sign in to join the conversation!

Quick Links

Trending discussions

Status Log Push to Dashboard
I want to push the latest status log sheet entry to the dashboard. I have added working columns for the last date, recent value (0 or 1) and recent status (text field). The recent columns are blank except for the most recent data rows (that part of the sheet is working). I have a project metric sheet template across topics…
Calculate Project Time TOTAL for PM across aligned projects
Hi - I am trying to figure out how to create a SUM of the Total Project Time aligned to each PM for all of their projects. On my master sheet I have the Resource Type, Resource Name, and a Helper Column for Time. The Helper Time is an estimated allotted time based on the timeframe of the project. Example: <3 months = 25…
Sheet Summary Formula
I am looking to count how many letters we have issued within each month, this is by going by the modified date, these are the formulas i have tried as i want to start this from August: =COUNTIFS(Modified:Modified, MONTH(@cell ) = 8, Modified:Modified, YEAR(@cell ) = 2025) However, it comes up with an error. Any ideas…