Need help in AverageIf formula with 2 condition

Sivakumar CSivakumar C ✭✭✭✭
edited 05/09/21 in Formulas and Functions
05/09/21 Edited 05/09/21
Accepted

Hi team, 

I need help with an AverageIF formula. I am working on the request tracking system for Import shipment. 

I would like to calculate the average days in transit with 2 conditions, 1. Country (USA, UK, Sweden, etc), 2. Mode of transit (Air mode, Ocean Mode, Courier mode) + Average days in transit. 

Existing AverageIF formula, I can able to get the average days with 1 condition only. 

=AVERAGEIF({Import Shipment Request Intake Range 5}, "Air mode", {Import Shipment Request Intake Range 1})

Can anyone help me to resolve this request? 

Best Answer

Answers

  • Mark CronkMark Cronk ✭✭✭✭✭
    Accepted Answer

    Hi @Sivakumar C ,

    AVERAGEIF only allows 1 criteria. Try using AVG(COLLECT(........). Here is the syntax:

    https://help.smartsheet.com/function/collect

    Work for you?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Sivakumar CSivakumar C ✭✭✭✭

    Thank you @Mark Cronk ,

    I had tried with Avg(Collect(...) function, it's working.

    Below is the final formula which worked for me.

    =AVG(COLLECT({Import Shipment Request Intake Range 6}, {Import Shipment Request Intake Range 8}, "USA", {Import Shipment Request Intake Range 5}, "Air Mode"))

  • Mark CronkMark Cronk ✭✭✭✭✭

    Excellent. Glad you found a solution. Thank you for contributing to the Community.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Sign In or Register to comment.