Using Averageifs function in excel with multiple condition

[xenos14]

Gurus,

I need to average a column say B:B (with dollars) only if Column A:A contains either A or B or E. Any help will be highly appreciate.

Regards,

Xenos

[Fotis1991]

Hi

I am not a Guru..but you can try, something like this.

=IF(COUNTIF(A:A;"A")+COUNTIF(A:A;"B")+COUNTIF(A:A;"C")>0;AVERAGE(B:B);"")

Hope to helps you

[Ron Coderre]

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

Please Login or Register  to view this content.

Is that something you can work with?

[xenos14]

Thanks for the prompt reply but after using your formula the answer is not coming correct.

[xenos14]

Thanks for the prompt reply but after using your formula the answer is not coming correct

[xenos14]

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

Please Login or Register  to view this content.

Is that something you can work with?

Thanks Ron,

How to use same example for countif?

[Fotis1991]

..An example is here..

[Ron Coderre]

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

[Fotis1991]

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

Thanks Ron

First time see this...

I dIdn't knew this!

[mickhammond]

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

Please Login or Register  to view this content.

Can this be done while excluding cells that are blank?
Also can the conditions "a", "b", "e" be a reference to another cell?

Thanks