count if equals first non error cell

J

John

Hi, I want to count the number of times the number 3 or -3 comes up in A8:A15
depending on whether 3 or -3 comes up first. If 3 is A8 then count how many
times 3 is found in the range... if A8 is an error (#n/a) and A12 is the
first non error row and equals -3, count how many times -3 is in the origanal
range.

Thanks for the help!
 
T

T. Valko

Are there any other numbers in the range besides 3 or -3? Can you post an
example of the data in the range and let us know what result you expect?
 
J

John

A8 #n/
A9 #n/
A10
A11 -
A12#n/
A13 #n/
A14
A15

result would be 3, since positive 3 occurred 3 times in the range and was the first non error result. 3 and -3 are only values aside from #N/A
 
A

Ashish Mathur

Hi,

Try this

=COUNTIF(E5:E13,INDEX(E5:E13,MATCH(TRUE,INDEX((ISNUMBER($E$5:$E$13)),,1),0),1)).
Please change the range references to A8:A15

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
T

T. Valko

=COUNTIF(E5:E13,INDEX(E5:E13,MATCH(TRUE,INDEX((ISNUMBER($E$5:$E$13)),,1),0),1))

If there are no numbers in the range that formula ends up counting how many
#N/A's are in the range.

Try this one:

=IF(COUNT(A8:A15),COUNTIF(A8:A15,INDEX(A8:A15,MATCH(1,INDEX(--ISNUMBER(A8:A15),0),0))),"")

--
Biff
Microsoft Excel MVP


Ashish Mathur said:
Hi,

Try this

=COUNTIF(E5:E13,INDEX(E5:E13,MATCH(TRUE,INDEX((ISNUMBER($E$5:$E$13)),,1),0),1)).
Please change the range references to A8:A15

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top