count if equals first non error cell

  • Thread starter Thread starter John
  • Start date Start date
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!
 
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?
 
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
 
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
 
=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
Click to expand...
 
Back
Top