How to Get the Average of a Range Based on 2 Conditions

**justinprime** · 02-06-2013, 03:40 PM

I want to the get the average of all entries in N:N. The values included in the average must meet either one of these criteria

1. equal "4" in C:C
2. equal "5" in C:C

Said plainly - I would like the average of all values in N:N that are either a "4" or a "5" in C:C.

Can't get the syntax right. Thanks.

**daddylonglegs** · 02-06-2013, 03:46 PM

Try this "array formula"

=AVERAGE(IF(C2:C100={4,5},N2:N100))

confirmed with CTRL+SHIFT+ENTER

or you can use a non-array version like this

=SUM(SUMIF(C:C,{4,5},N:N))/SUM(COUNTIF(C:C,{4,5}))

**justinprime** · 02-06-2013, 04:01 PM

The array formula worked great, thanks. I couldn't figure out how to get the array syntax working for my problem. Much appreciated.

**Ronjon101** · 03-25-2013, 04:19 PM

Great work guys, can either of you make available a spreadsheet I can download and try out? I think this might solve my question/problem that I have been searching for answers for on a lot of threads. I am a newbie and any extra help is greatly appreciated. Thanks again.