Problem with average number formula, with certain criteria

Jeffrey Wagemans

Hi All!

Im trying to get the average number of a certain range, but only if it meets a certain criteria. I have looked at other examples but somehow i don't get it to work.

So I want the average of the blue columns. But only if the Red column is a certain name. And if its possible I want to do the formula on a seperate sheet??

Does anyone have any ideas how to make this work?

Paul Newcome

You are going to want to insert an additional "helper" column on the source sheet that contains the averages per row. You can then hide that column to help keep the sheet clean.

=AVG([First Blue Column]@row:[Last Blue Column]@row)

Then (using the appropriate method to create cross sheet references) your formula on the target sheet would look similar to...

=AVG(COLLECT({Source Sheet Helper Column}, {Source Sheet Name Column}, "John Doe"))

Paul Newcome

You are going to want to insert an additional "helper" column on the source sheet that contains the averages per row. You can then hide that column to help keep the sheet clean.

=AVG([First Blue Column]@row:[Last Blue Column]@row)

Then (using the appropriate method to create cross sheet references) your formula on the target sheet would look similar to...

=AVG(COLLECT({Source Sheet Helper Column}, {Source Sheet Name Column}, "John Doe"))

Jeffrey Wagemans

Thanks Paul it worked!!

Paul Newcome

Happy to help! 👍️