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