average-if-and formula

[Guest]

{=AVERAGE(IF(AND(B2:B13241=1,D213241=2),N2:N13241,""))}

why doesn't this formula work? thanx!

 

[Guest]

one way

=SUMPRODUCT((B2:B13241=1)*(D213241=2)*(N2:N13241))/SUMPRODUCT((B2:B13241=1)*(D213241=2)*(N2:N13241<>0))


Mike

 

[Don Guillett]

Use this. Array enter.
=AVERAGE(IF((B2:B22=1)*(D222=2),N2:N22))

 

[Stephen]

{=AVERAGE(IF(AND(B2:B13241=1,D213241=2),N2:N13241,""))}

why doesn't this formula work? thanx!

Because you can't use AND with arrays (as it returns only a single result,
not an array). Instead you need to multiply the two arrays:
{=AVERAGE(IF((B2:B13=1)*(D213=2),N2:N13,""))}

 

[Guest]

{=AVERAGE(IF(AND(B2:B13241=1,D213241=2),N2:N13241,""))}

You could also try it slightly amended like this, array-entered with CSE*:
=AVERAGE(IF((B2:B13241=1)*(D213241=2),N2:N13241))

*CSE = Press CTRL+SHIFT+ENTER to confirm the formula

---

 

[Guest]

Thank you all!

Because you can't use AND with arrays (as it returns only a single result,
not an array). Instead you need to multiply the two arrays:
{=AVERAGE(IF((B2:B13=1)*(D213=2),N2:N13,""))}