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

lhumphrey

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.

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.

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.