Cross Reference Spreadsheet



  • Genevieve P.
    Genevieve P. Employee Admin

    Hi Thomas,

    It sounds like you're looking for two different formulas:

    1. to COUNT the number of times a name appears in a different sheet
    2. to AVG the % column per-person

    Is that correct? If so, in your PM Totals sheet, you will want to have a column with the Names to reference. Then you can build the following two cross-sheet formulas:

    1 . Total Number Rows with that name

    =COUNTIF({Column with Names in other sheet}, =[Name Column]@row)

    2 . AVG the % column per-person

    =AVERAGEIF({Column with Names in other sheet}, =[Name Column]@row, {Column with % in other sheet})

    Here are some Help Center articles I used for this: COUNTIF function / AVERAGEIF function / @row function / Cross Sheet References

    Let me know if you have any questions about this! It may help to see screen captures of your sheet to help further (but please block out any sensitive data).



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!