Autonumbering formula that ignores empty cells containing formulas and consider them blank

[edge37]

Hello, I'm trying to get a working formula for autonumbering cells (B9:B38) only when they have visible values on them. And when there are blank cells or cells that has a formula with no visible values, the numbering stops or jumps to the next cell with values. I'm trying the formula:

=IF(LEN(B9)=0,"",(COUNTA($B$9:B9)))

And it would work great if the cells that have a formula, but appears empty, would be ignored and considered blank. The formula numbers every cell in the range because all of them have formulas (the formula is just a reference to other cells in a different worksheet of the same file), If I erase the formula in any of those cells, the autonumbering works perfect. Please check the pic where I also explain the case.

Thank you very much

 

[edge37]

Those cells are unlikely to be "blank" but will probably have a 0 in them. So you need to count <70 & >0

I put it like this:

=IF(D6:D11=0,"",COUNTIFS(D6:D11,"<70 & >0"))

but still do not work, I get a #SPILL! result (did I place the correction wrong?)
Summary: I need to count how many students have <70 grade in column D, excluding cells with formulas but blank.

 

[edge37]

Those cells are unlikely to be "blank" but will probably have a 0 in them. So you need to count <70 & >0

I tried to put this in the formula:

=IF(D6:D14=0,"",COUNTIFS(D6:D14,"<70","<>0"))

but it gives me a #SPILL! error, maybe I did it wrong.
What I need is to count how many grades are <70 in column D6:D14, but excluding cells that appear blank eventhough they have a formula in them. Thank you

CUADRO DE NOTAS 2025.xlsm
	B	C	D
5	1	JORGE	95
6	2	LUIS	66
7	3	ANA	100
8	4	JOSE	88
9	5	PEDRO	69
10	6	JUAN	95
11	7
12	8
13	9
14	10
SUMMARY

Cell Formulas
Range	Formula
D5:D13	D5	='10A (1)'!$R9

 

[edge37]

sorry for the double post

 

[Fluff]

Try

=COUNTIFS(D6:D14,"<70",D6:D14,">0")

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Count cells with values, excluding blanks & cells that have a formula but appear blank
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[edge37]

Try

=COUNTIFS(D6:D14,"<70",D6:D14,">0")

Thank you very much, works great.

 

[Fluff]

You're welcome & thanks for the feedback.