Need help in AverageIf formula with 2 condition
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
-
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.
Answers
-
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.
-
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"))
-
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.
Help Article Resources
Categories
Check out the Formula Handbook template!