Need help in AverageIf formula with 2 condition

Sivakumar C

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? 

Mark Cronk

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

Mark Cronk

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

Sivakumar 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 Cronk

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

Mark