formula Counta

**tangomj** · 03-05-2007, 09:26 PM

Using H1=COUNTA(F:F)/COUNTA(E:E) works fine if the formula is in a different column other than F or E. If the formula is placed on column E1 the result is 0. What changes are needed to make it work under the same column?

**davesexcel** · 03-05-2007, 09:32 PM

Originally Posted by tangomj

Using H1=COUNTA(F:F)/COUNTA(E:E) works fine if the formula is in a different column other than F or E. If the formula is placed on column E1 the result is 0. What changes are needed to make it work under the same column?

just a thought,
if you place the formula in E1 then you need to say E2:E65536

**tangomj** · 03-05-2007, 11:26 PM

that works for me. thanks.

Now I have:
=COUNTA(C$3:C$6536)/COUNTA($B$3:$B$65536)

Next step:
Within the data range (column B) all cells without a value will have a "*" instead of a blank. All cells outside the data range (column B) will be blank. How can I use the above formula while excluding all "*" and blank cells in the total count?

**Paul** · 03-05-2007, 11:35 PM

You can try:

=IF(OR(B3="*",B3=""),"",COUNTA(C$3:C$6536)/COUNTA($B$3:$B$65536))

If B3 is * or blank, then the formula won't show any result. If it's not either of those then it will perform your division.

**tangomj** · 03-06-2007, 12:33 AM

I get a FALSE only. Here's my column C data:

25
58
*
36
*
14
68
*
98
<blank>
..... <blank>

How do I do COUNTA with the numerial values while excluding the * and blanks? Column B is donimator. Column C is the numerator. Dynamic data is on column C.

**Paul** · 03-06-2007, 12:45 AM

Can you please upload a spreadsheet with some sample data as you would be working with it. Earlier you said *'s and blanks would be in column B, now they're in column C?

**tangomj** · 03-06-2007, 12:49 PM

I've attached the file with an example. I'm still getting a FALSE result instead of a percentage.

**daddylonglegs** · 03-06-2007, 01:27 PM

Try this formula in B1 copied across to C1 and D1

=COUNT(B3:B1000)/COUNT($A3:$A1000)

**tangomj** · 03-06-2007, 02:08 PM

Thanks. Simple solution.

**Sean Anderson** · 03-07-2007, 12:33 PM

Does anyone know why the =CountA function doesn't work with named ranges. For example, let's say the data is in cells A1:A10 and we name that range "Range1", the following counta function doesn't work.

=COUNTA(Range1)

but the following does work

=COUNTA(A1:A10)