Problem with average number formula, with certain criteria
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?
Best Answer

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"))
Answers

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"))

Thanks Paul it worked!!

Happy to help! 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!