How Do I Pull Data from Two Sheets to Summarize Output per Hour?

I'm working with two grid sheets that include various columns of information. Each has multiple rows of information. We have Smartsheet Pivot to help pull data together. Here is a simplified example:


Grid 1

Columns: Ticket#, Footage, Value, Date

Grid 2

Columns: Employee, Hours, Date


I'd like to total the footage for a particular date, total the hours for that same date, then get a footage/hour. How do I do that? I haven't used VLookup before, but thought that might be involved. Thanks in advance for the help.


Thanks,

Vincent

Answers

  • Antonio Figueroa
    Antonio Figueroa ✭✭✭✭✭✭

    Good Morning


    I would use a sumif fucntion. See Below

    =IFERROR(SUMIF([Footage Date]3:[Footage Date]5, [Look Up value]@row, [Footage QTY]3:[Footage QTY]5) / SUMIF([Hours Date]3:[Hours Date]5, [Look Up value]3, [Hours QTY]3:[Hours QTY]5), "")

    First Create a New sheet with the left two columns. In second column put some dates in it that you want to calculate. In the first column use the above formula with your column and sheet names. Third and Fourth Column represents your footage sheet. The fifth and sixth column represents your Hours Sheet. So make sure when you write the formula you reference your sheets.


    Hope this helps

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!