counta formula
 

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

counta formula
 

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

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

Does that help?
--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.


"excelguy" wrote:

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

counta formula
 

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




"JBeaucaire" wrote:

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

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

Does that help?
--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.


"excelguy" wrote:

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

counta formula
 

Try this:

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


"excelguy" wrote:

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