using sum(collect()) with an or in the criterion range

Options

Sheet A has three contact columns per row.

Sheet B has a list of personnel.

I'd like to collect the rows where a person appears in any of the three contact columns from Sheet A and sum on a numeric column

My current solution is to write three sum(collects()) - one matching each contact column - and add the result.

Is there a way to incorporate an or() into the criterion_range to simplify this?

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    Hi @Tim Machula 

    Hope you are fine, did you mean that in your sheet B for each contact in sheet A you count how many time this contact X repeated in each of the 3 contact columns?

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    Hi @Tim Machula 

    Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!