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

edge37

Board Regular
Joined
Sep 1, 2016
Messages
102
Office Version
  1. 365
Platform
  1. Windows
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:
Excel 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
 

Attachments

  • Screenshot_66.jpg
    Screenshot_66.jpg
    121.3 KB · Views: 12

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
How about
Excel Formula:
=IF(B9=0,"",COUNTIFS($B$9:B9,"<>0"))
 
Upvote 0
Solution
Assuming that column B is actually returning "" then possibly:
Excel Formula:
=IF(B9="","",COUNTIFS($B$9:B9,"?*"))
 
Upvote 0
.. but they cannot both work? :huh:
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.
 
Upvote 0
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.
 
Upvote 0
=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:
Excel Formula:
=IF(D6:D11=0,"",COUNTIFS(D6:D11,"<70","<>0"))

Can you fix it, please? Thanks in advance
CUADRO DE NOTAS 2025.xlsm
BCD
2#ESTUDIANTES1ᵉʳ
31JORGE95
42LUIS66
53ANA100
64JOSE88
75PEDRO69
86JUAN95
97 
108 
119 
1210 
1311 
1412 
SUMMARY
Cell Formulas
RangeFormula
D3:D14D3='10A (1)'!$R9
 
Upvote 0
Those cells are unlikely to be "blank" but will probably have a 0 in them. So you need to count <70 & >0
 
Upvote 0

Forum statistics

Threads
1,224,872
Messages
6,181,499
Members
453,047
Latest member
charlie_odd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top