Using Averageifs function in excel with multiple condition

**xenos14** · 01-19-2012, 12:54 PM

Originally Posted by Ron Coderre

This regular formula averages the Col_B cells where the corresponding Col_A cell equals any of a, b, or e

C1: =SUM(SUMIF(A:A,"="&{"a","b","e"},B:B))/SUM(COUNTIF(A:A,"="&{"a","b","e"}))

Is that something you can work with?

Thanks Ron,

How to use same example for countif?

**Fotis1991** · 01-19-2012, 01:01 PM

..An example is here..

**Ron Coderre** · 01-19-2012, 05:39 PM

Originally Posted by xenos14

Thanks Ron,

How to use same example for countif?

In this part of the formula I posted:
SUM(COUNTIF(A:A,"="&{"a","b","e"}))

This section: "="&{"a","b","e"}
resolves to these three distinct criteria:
="a"
="b"
="e"
(The braces {} define the beginning and end of the array of values)

Consequently, this: SUM(COUNTIF(A:A,"="&{"a","b","e"}))
is equivalent to this:
SUM(COUNTIF(A:A,="a"),COUNTIF(A:A,="b"),COUNTIF(A:A,="e"))

I hope that helps