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

 

[Fluff]

How about

=IF(B9=0,"",COUNTIFS($B$9:B9,"<>0"))

 

[Alex Blakenburg]

Assuming that column B is actually returning "" then possibly:

=IF(B9="","",COUNTIFS($B$9:B9,"?*"))

 

[edge37]

@Fluff
@

Alex Blakenburg​

Those formulas will do just fine. Thank you very much

 

[Alex Blakenburg]

You're welcome. Glad we could help.

 

[Peter_SSs]

Those formulas will do just fine.

.. but they cannot both work?

 

[edge37]

.. but they cannot both work?

Actually the first one is the one that Im using now. The second works even though I have it "in reserve" because a little detail keeps it from being used according to my needs.

 

[Peter_SSs]

It was a "little detail" that I was commenting on.
If your column B formulas pulling values from the Summary sheet are ..

• returning 0 values (hidden by formatting) then Fluff's will work and Alex's will not
• returning "" values then Alex's will work and Fluff's will not
• returning a mixture of "" & hidden 0 (or anything else hidden) then neither will work.

 

[edge37]

=IF(B9=0,"",COUNTIFS($B$9:B9,"<>0"))

Hi Fluff, can you helpe please. based on your formula here I tried to use COUNTIF for values <70 in a column that have cells with formulas but appear blank. I've tried the following variations in my formula to ignore blank cells and cells with formulas in column D: "<>", "<>""",">0". Nothing is getting me the correct count. Is there anything else I need to add? The formula I tried is:

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

Can you fix it, please? Thanks in advance

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

Cell Formulas
Range	Formula
D3:D14	D3	='10A (1)'!$R9

 

[Fluff]

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