if-statementgoogle-sheetslambdagoogle-sheets-formulasequence
Series Formula is correct but output is incorrect
I used one formula for series, in which whenever there is any value in a particular cell then the adjacent cell will show the serial number, if empty then the serial number column will also be empty. The Formula I used is:"=IF(B3:B="","",ROW()-2)" ( "-2" because there are 2 rows which have headings) Then I used "=Match143^143,B3:B)" to get the last cell value.
I used the above 2 formulas for 2 sheets, it worked fine for a few days but now as the datasets increased it is showing a total count -2. Suppose the total count is 100 but is showing 98.
I used this formula ( "=IF(B3:B="","",ROW())" ) as well to check but it is also showing a difference of 2.
Thank you in advance
Solution
try:
=INDEX(IF(B3:B="",,COUNTIFS(B3:B, "<>", ROW(B3:B), "<="&ROW(B3:B))))
to make it faster use:
=INDEX(LAMBDA(x, IF(x="",,COUNTIFS(x, "<>", ROW(x), "<="&ROW(x))))
(B3:INDEX(B:B, MAX(ROW(B:B)*(B:B<>"")))))
or:
=INDEX(LAMBDA(z, IF(z="",,SCAN(, z<>"", LAMBDA(x, y, x+y))))
(B3:INDEX(B:B, MAX(ROW(B:B)*(B:B<>"")))))
- Batch: Comparing version numbers via GEQ when said version uses dots and varying number of digits
- Is there a way I can reduce this in-a-row kind of repeating Lua conditions?
- two if or more with one ELSE
- Simplifying If Statements Into Loop
- Add 95 to a cell if the name and date in that row match another row with the same name and date
- Is there a way to do 24-hour format and 60-minute format in Java?
- Using IF in function for different queries as a result
- Fat-Free Framework Template Conditional Rendering Issue on Template
- Print a multiplication table in Python
- Can you store a variable inside a if-clause?
- How do I test for exceptions in if-statements in python?
- How to write lambda function with three conditions
- Matching multiple number rows and check corresponding dates within 90 days
- Sum all cells for which corresponding cell is not a formula and doesn't return #VALUE AND date occurs in the past
- If condition that matches if grep outputs at least one result even though the exit code is 2 due to some permission denied
- Why does `ifelse()` behave differently than `if (cond) {...} else {...}`?
- A Return inside and outside an If Statement
- How can I use "*ngIf else"?
- How to check repeated letters in a string c#
- How to create an 'empty if statement' in Python
- How to avoid if statement? for the compiler cannot optimize it to simd
- Test if it exists a node HXT
- python else statement causes a continuous loop
- Series of if vs else-if statements benchmarking
- Google Sheets - make a list of the names in column A where column E is greater than zero
- Condition evaluation process in Java
- Excel - Array Formula to skip empty cells
- Groups of compound conditions in Bash test
- How to use the IF statement to avoid the Weather API fetch result crashing once the user types wrong the city name?
- Comparing Text with a lot of If/else