No idea where to start?!?!?

ccousineau

Hi all! New to smartsheet and struggling with formulas.

Here is the sheet I am currently working with (Sheet A)

I want the column labeled DC AVG to populate the total AVG for all Doc Control lines, from the Date of Inspection and any dates prior. (I manually populated the existing data in DC AVG)

Similarly for LP AVG, I want this column to populate the total AVG for Launch Pad lines, from the date of inspection and any dates prior.

I would like to do this for Warehouse lines as well and label that as WH AVG

I would like the cell to be BLANK if it does not meet the criteria.

I do have a source sheet (Sheet B) with all of the same info that generates new lines from form submissions. I would like to reference that sheet if possible, but the complexity of that might be too mind melting for me..

Any assistance would be GREAT!! Thanks :)

Peggy Parchert

Hello @ccousineau

Hope you are doing well. Would this work for what you need?

Using the first three columns in your sheet screenshot above, Area, Date of Inspection and AVG, I created the following Sheet Summary fields:

DC AVG - formula: =SUMIF(Area:Area, "Doc Control", AVG:AVG) / [Count DC]#

Count DC - formula: =COUNTIF(Area:Area, "Doc Control")

LP AVG - formula: =SUMIF(Area:Area, "Launch Pad", AVG:AVG) / [Count LP]#

Count LP - formula: =COUNTIF(Area:Area, "Launch Pad")

WH AVG - formula: =SUMIF(Area:Area, "Warehouse", AVG:AVG) / [Count WH]#

Count WH - formula: =COUNTIF(Area:Area, "Warehouse")

These could then be viewed in a Summary report or you could add a header row to your sheet (using the Summary Sheet fields) - something like this maybe:

The columns would have the following formulas:

DC AVG - =[DC AVG]#

LP AVG - =[LP AVG]#

WH AVG - =[WH AVG]#

All these formulas would update as lines were added. For your Sheet B, do you move lines to Sheet A?

Peggy

Peggy Parchert

@ccousineau

Happy to help! For moving rows from one sheet to another sheet, you can setup an automation to do this (based off a trigger). Here are some good articles on moving rows:

Automatically move or copy rows between sheets

Best Practices for Working With the Move Rows Action

To create the graph, I would create a separate metrics sheet (similar to what I have below - minus the rows with the formulas.):

Then you can use this data to create reports or charts/metrics on a dashboard.

I added more data to your original screenshot to get things to look right and two helper columns: Qtr & Year.

Formulas:

Qtr: =IFERROR("Q" + IF(MONTH([Date of Inspection]@row) >= 10, "4", IF(MONTH([Date of Inspection]@row) >= 7, "3", IF(MONTH([Date of Inspection]@row) >= 4, "2", "1"))), "")

Year: =IFERROR(YEAR([Date of Inspection]@row), "")

Created this dashboard to show you both metrics widgets and a chart widget.

Let me know if this isn't what you were thinking. Happy to share the sheets/reports/dashboard with you if you would like.

Peggy

Peggy Parchert

Hello @ccousineau

Hope you are doing well. Would this work for what you need?

Using the first three columns in your sheet screenshot above, Area, Date of Inspection and AVG, I created the following Sheet Summary fields:

DC AVG - formula: =SUMIF(Area:Area, "Doc Control", AVG:AVG) / [Count DC]#

Count DC - formula: =COUNTIF(Area:Area, "Doc Control")

LP AVG - formula: =SUMIF(Area:Area, "Launch Pad", AVG:AVG) / [Count LP]#

Count LP - formula: =COUNTIF(Area:Area, "Launch Pad")

WH AVG - formula: =SUMIF(Area:Area, "Warehouse", AVG:AVG) / [Count WH]#

Count WH - formula: =COUNTIF(Area:Area, "Warehouse")

These could then be viewed in a Summary report or you could add a header row to your sheet (using the Summary Sheet fields) - something like this maybe:

The columns would have the following formulas:

DC AVG - =[DC AVG]#

LP AVG - =[LP AVG]#

WH AVG - =[WH AVG]#

All these formulas would update as lines were added. For your Sheet B, do you move lines to Sheet A?

Peggy

ccousineau

@Peggy Parchert Thank you for this response!! This helps me tremendously in working with sheet summaries.

If I wanted to take it a step further, how could I move lines from Sheet B to Sheet A?

Also, is it possible to make a trend graph in smartsheet showing avg by area over time?

I'm looking to visualize my data in as many ways as possible for further understanding and practice of smartsheet.

Thanks so much!

Peggy Parchert

@ccousineau

Happy to help! For moving rows from one sheet to another sheet, you can setup an automation to do this (based off a trigger). Here are some good articles on moving rows:

Automatically move or copy rows between sheets

Best Practices for Working With the Move Rows Action

To create the graph, I would create a separate metrics sheet (similar to what I have below - minus the rows with the formulas.):

Then you can use this data to create reports or charts/metrics on a dashboard.

I added more data to your original screenshot to get things to look right and two helper columns: Qtr & Year.

Formulas:

Qtr: =IFERROR("Q" + IF(MONTH([Date of Inspection]@row) >= 10, "4", IF(MONTH([Date of Inspection]@row) >= 7, "3", IF(MONTH([Date of Inspection]@row) >= 4, "2", "1"))), "")

Year: =IFERROR(YEAR([Date of Inspection]@row), "")

Created this dashboard to show you both metrics widgets and a chart widget.

Let me know if this isn't what you were thinking. Happy to share the sheets/reports/dashboard with you if you would like.

Peggy

ccousineau

@Peggy Parchert You are AMAZING!!!!! Thank you!!!!

ccousineau

@Peggy Parchert The only issue I am running into is the Launch Pad AVG column will only populate the same number of 3.14

It is set up identically to the other columns, but it is the only one having this issue.

I've moved everything over to one sheet so I'm not referencing any other sheets.

This is what the formula says now..

=IFERROR((SUMIFS(AVG:AVG, Qtr:Qtr, Qtr@row, Year:Year, Year@row, Area:Area, "Launch Pad") / (COUNTIFS(Area:Area, "Launch Pad", Qtr:Qtr, Qtr@row, Year:Year, Year@row))), "")

Qtr: =IFERROR("Q" + IF(MONTH([Date of Inspection]@row) >= 10, "4", IF(MONTH([Date of Inspection]@row) >= 7, "3", IF(MONTH([Date of Inspection]@row) >= 4, "2", "1"))), "")

Year: =IFERROR(YEAR([Date of Inspection]@row), "")

Peggy Parchert

@ccousineau

Good morning. Did you create a separate metrics sheet or is all your metrics in your Sheet A (along with all the rows from Sheet B)?

Peggy

Peggy Parchert

@ccousineau

I think I know the issue - in your first screenshot above, Launch Pad Inspection dates are all in the same quarter. So the number would be the same. I added additional data to my Sheet A so that I could make the chart look complete.

When you moved rows from Sheet B to Sheet A, were any of them for Launch Pad?

Peggy

ccousineau

@Peggy Parchert all of my metrics are on Sheet A now. I did move some Launch Pad metrics from Sheet B to Sheet A, yes.

I knew it had to have something to do with the Qtr being the same for all of the Launch Pad entries.. Is this something that will correct itself as more data is entered in the sheet or do you think it's a formula issue?

ccousineau

I have added two more columns: Office Supply Cabinets AVG and BOPS Storage Closet AVG. I'm looking to use the same formula structure but when I do, it populates a number in both columns every time. I feel like this is probably something small I'm over looking?

=IFERROR((SUMIFS(AVG:AVG, Qtr:Qtr, Qtr@row, Year:Year, Year@row, Area:Area, "Office Supply Cabinets") / (COUNTIFS(Area:Area, "Office Supply Cabinets", Qtr:Qtr, Qtr@row, Year:Year, Year@row))), "")

=IFERROR((SUMIFS(AVG:AVG, Qtr:Qtr, Qtr@row, Year:Year, Year@row, Area:Area, "BOPS Storage Closet") / (COUNTIFS(Area:Area, "BOPS Storage Closet", Qtr:Qtr, Qtr@row, Year:Year, Year@row))), "")

Peggy Parchert

@ccousineau -

I apologize, not sure what happened to my response from yesterday to your question about Launch Pad lines but the formula is correct - you just need more lines for Launch Pad in a different quarter.

I recommend that you create a separate metrics sheet for this (see the one I created below).

Having everything in one sheet is confusing the formula to grab data from incorrect lines. I attempted to use the formula by adding the two new columns you listed above into the Sheet A that I had created and it is pulling from lines with Doc Control and Launch Pad.

Let me know if this isn't an option.

Peggy