counta formula

[excelguy]

I have the formula =10/counta(E6:E15 ).
This gives me 10/10 if E6 through E15 are filled.

How do I make this formula:
=10/counta( E6:E15 but exclude the cells from this range that equal any of
the numbers in B5:B8)


Example:
=10/counta(E6:E15) would be 10/10 if E6:E15 are filled

but if E12 = B5, excel would give me 10/9 because E12 would be excluded

 

[JBeaucaire]

This is an array formula, confirm it by pressing CTRL-SHIFT-ENTER:

=10/COUNTIF(E6:E15, "<>" & B5:B8)

Does that help?

 

[excelguy]

no this doesn't work. how is an array formula suppose to operate?

 

[Teethless mama]

Try this:

=10/(10-SUMPRODUCT(COUNTIF(E6:E15,B5:B7)))