I would like to know how to calculate variance (VAR, VARA, VARP) with criteria as in AVERAGEIF and AVERAGEIFS. The VARIF function does not seem to exist. This question also applies to others functions such as median, etc.

1 Answer 1


The FILTER function can be used to first select the parts of the range that meet your criteria. That result can then be passed to VAR (e.g. =VAR(FILTER(source_range, condition))).

FILTER can also be used to filter one column based on a condition on another column. For example, if you have columns:

x | 1
y | 2
x | 3

You can find the VAR for the rows that have x in the first column like this:

=VAR(FILTER(B:B, A:A = "x"))
  • The problem is that I want to apply the filter in one column and to the variance of another. For example if I have a table with 2 columns like this I want to filter for X then do variance with 1 and 7: x 1 z 2 x 7
    – Legion
    May 24, 2017 at 9:33
  • @Legion Updated the answer to include info on how to filter in one column and get the values from another column. You might also want to update your original question to be more specific. May 24, 2017 at 17:23
  • @Legion: That's important information to include in your question. Comments are meant to be temporary.
    – ale
    May 24, 2017 at 21:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.