SOLVED

counting rows sequentially in a column for a large spreadsheet

%3CLINGO-SUB%20id%3D%22lingo-sub-2512861%22%20slang%3D%22en-US%22%3Ecounting%20rows%20sequentially%20in%20a%20column%20for%20a%20large%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2512861%22%20slang%3D%22en-US%22%3E%3CP%3EI%20attached%20the%20problem%2C%20basically%2C%20I%20am%20trying%20to%20start%20counting%20in%20cell%20e3%2C%20which%20is%20the%20cell%20that%20has%20nothing%20above%20it%2C%20and%20the%20cell%20that%20has%20no%20name%20in%20it%20like%20in%20cell%20A2.%26nbsp%3B%20when%20I%20get%20done%20counting%20sequentially%20at%20the%20bottom%2C%20there%20will%20be%20a%20blank%20with%20a%20new%20string%20in%20A44%2C%26nbsp%3B%20once%20that%20happens%20I%20want%20to%20start%20over.%26nbsp%3B%20I%20need%20to%20be%20able%20to%20paste%20this%20all%20the%20way%20down%20where%20it%20starts%20over%2C%26nbsp%3B%20the%20string%20in%20a2%20is%20a%20name%2C%20and%20the%20descending%20order%20in%20column%20c%20needs%20to%20stay%20descending.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2512861%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2513001%22%20slang%3D%22en-US%22%3ERe%3A%20counting%20rows%20sequentially%20in%20a%20column%20for%20a%20large%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2513001%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1094529%22%20target%3D%22_blank%22%3E%40bdog2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EOne%20way.%20In%26nbsp%3B%3CSTRONG%3EE3%3C%2FSTRONG%3E%20and%20copy%20down%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFS(AND(ISNUMBER(A3)%2CNOT(ISNUMBER(E2)))%2C1%2C%20NOT(ISNUMBER(A3))%2C%22%22%2C%20TRUE%2CE2%2B1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EUpdated%20sample%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2513003%22%20slang%3D%22en-US%22%3ERe%3A%20counting%20rows%20sequentially%20in%20a%20column%20for%20a%20large%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2513003%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1094529%22%20target%3D%22_blank%22%3E%40bdog2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheck%20the%20modified%20formula%20in%20cell%20E4%2C%2C%2C%20I%20do%20believe%20this%20is%20what%20your%20were%20looking%20for%2C%2C%2C%20if%20my%20suggested%20formula%20works%20for%20you%20then%20you%20may%20accept%20my%20post%20as%20Best%20Answer%20as%20well%20Like.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2513214%22%20slang%3D%22en-US%22%3ERe%3A%20counting%20rows%20sequentially%20in%20a%20column%20for%20a%20large%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2513214%22%20slang%3D%22en-US%22%3E%3CP%3Ethank%20you%26nbsp%3B%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F75890%22%20target%3D%22_blank%22%3E%40L%20z.%3C%2FA%3E%26nbsp%3B%20%2C%20this%20is%20excellent.%26nbsp%3B%20one%20question%20though%2C%20%3F%26nbsp%3B%20would%20be%20for%20some%20reason%20when%20I%20double%20click%20to%20copy%20down%20the%20larger%20version%20of%20spreadsheet%20it%20stops%20and%20I%20have%20to%20manually%20drag%20it%20down.%26nbsp%3B%20any%20suggestions.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I attached the problem, basically, I am trying to start counting in cell e3, which is the cell that has nothing above it, and the cell that has no name in it like in cell A2.  when I get done counting sequentially at the bottom, there will be a blank with a new string in A44,  once that happens I want to start over.  I need to be able to paste this all the way down where it starts over,  the string in a2 is a name, and the descending order in column c needs to stay descending. 

4 Replies
best response confirmed by bdog2021 (New Contributor)
Solution

@bdog2021 

Hi

One way. In E3 and copy down

=IFS(AND(ISNUMBER(A3),NOT(ISNUMBER(E2))),1, NOT(ISNUMBER(A3)),"", TRUE,E2+1)

Updated sample attached

@bdog2021 

Check the modified formula in cell E4,,, I do believe this is what your were looking for,,, if my suggested formula works for you then you may accept my post as Best Answer as well Like.

 

thank you  @L z.  , this is excellent.  one question though, ?  would be for some reason when I double click to copy down the larger version of spreadsheet it stops and I have to manually drag it down.  any suggestions. 

@bdog2021 

No idea why. Instead of using a Range format your data as a Table where formulas, cells format... are auto applied to new rows

 

If you go with the Table option:

- Delete the column (E in your sample) where the formula I suggested sits

- Format columns A:D as Table

- Enter something meaningful in E1 (the Table auto enlarge to inc. that new column)

- In E1:

=IFS(AND(ISNUMBER(A2),NOT(ISNUMBER(E1))),1, NOT(ISNUMBER(A2)),"", TRUE,E1+1)

 The formula should auto copy down till the end of the Table you created