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?

Find more posts tagged with

Formulas

Reports

Solution Center

functionality

Accepted answers

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

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.