counta formula

[excelguy]

I want to divide a number by counta for certain cells (which would yield a
number). For example, =10/counta(A1:A4) would equal 10/4 if cells A1 to A4
are filled.


What I am stuck on is this:

=10/counta(A1:A4 but exclude the cells from this range that equal any of
the numbers in in the range B5:B8)

Example:
=10/counta(A1:A4) would be 10/4
but since A2 = B7, the formula gives me 10/3 because A2 is excluded from
counta

 

[T. Valko]

What should happen if all the numbers match?

Try something like this:

=10/SUMPRODUCT(--(ISNA(MATCH(A1:A4,B5:B8,0))),A1:A4)

 

[JBeaucaire]

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

=10/COUNTIF(A1:A4,"<>"&B5:B8)

Does that help?

 

[T. Valko]

Ooops!

You wanted to divide by the count not the sum.

Try this:

=10/SUMPRODUCT(--(ISNA(MATCH(A1:A4,B5:B8,0))))

 

[excelguy]

if all the numbers match it would be:

=10/counta(A1:A4 but exclude the cells from this range that equal any of
the numbers in in the range B5:B8)

Excel would give me 10/0? o good point. To fix this I would always make the
exclusion range smaller than the regular range