counta formula
 

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

counta formula
 

What should happen if all the numbers match?

Try something like this:

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

--
Biff
Microsoft Excel MVP


"excelguy" wrote in message
...
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

counta formula
 

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

=10/COUNTIF(A1:A4,"<"&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 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

counta formula
 

Ooops!

You wanted to divide by the count not the sum.

Try this:

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
What should happen if all the numbers match?

Try something like this:

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

--
Biff
Microsoft Excel MVP


"excelguy" wrote in message
...
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

counta formula
 

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



"T. Valko" wrote:

What should happen if all the numbers match?

Try something like this:

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

--
Biff
Microsoft Excel MVP


"excelguy" wrote in message
...
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


.