• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

Average a range of numbers AVERAGEIF

I am trying to average a range of cells using an AVERAGEIF formula.
I only want to average if cell in A=4, then I want to average all numbers between columns S & AD.
What I am now realizing is that it is ONLY averaging column S, not columns S thru AD.
Is there a formula that will average all the numbers in all the columns?
  • 2
1 Solution
barry houdiniCommented:
You have to use an array formula, e.g.


confirmed with CTRL+SHIFT+ENTER

To do this place formula in cell then press F2 to select formula - hold down CTRL and SHIFT keys and press ENTER. If done correctly then curly braces like { and } will appear around the formula in the formula bar.

Note that the above will be quite slow because it looks at whole columns - best to limit to a fixed range, e.g. rows 1 to 100 like this


regards, barry
barry houdiniCommented:
....or you can use this longer "non-array" version


won't work if there is any text in S1:AD100.....

regards, barry
Euro5Author Commented:
Perfect - options & explainations. Can't do better than that!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now