Formula to average a range in another sheet corresponding to cells that contain parts of text

I can't seem to beat this formula. I've tried COUNTIF, COLLECT, CONTAINS, and when I'm not getting an error I'm getting 0.

I want to see the average of successful deliveries below for all Up to Date emails in Q1. In the sheet I'm working with I'm trying to say: If any cell in the Month/Year/Email column contains the words "Q1 2017 Up to Date" then show the average of successful deliveries. I'm working on this in another sheet where I'm referencing each column range.

Find more posts tagged with

Formulas

functionality

Accepted answers

L_123

Syntax error. I got the format of the contains formula wrong

=avg(collect({successful deliveries}, {Month/year/email},and(contains("Q1",@cell),contains("2017 Up to Date",@cell

My bad.

All comments

L_123

=avg(collect({successful deliveries}, {Month/year/email},and(contains(@cell, "Q1"),contains(@cell,"2017 Up to Date"

Give that a try and see if it works for you.

lhumphrey

@L@123 I'm getting a DIVIDE BY ZERO error

L_123

Syntax error. I got the format of the contains formula wrong

=avg(collect({successful deliveries}, {Month/year/email},and(contains("Q1",@cell),contains("2017 Up to Date",@cell

My bad.

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.