arraysgoogle-sheetscountformulaarray-formulas
Google sheets - Numerate rows between values in arrayformula
I have an arrayformula that numbers "chapters" based on finding the word "foo" in a range. I need it to also number the "sub-chapters" of those chapters, starting at 1 and resetting for each chapter.
A bit hard to explain but it's like this:
What I have
1 foo
2 foo
3 foo
What I need
1 foo
1
2
2 foo
1
2
3
3 foo
The arrayformula I have found works so far is this:
=ARRAYFORMULA(IF(C:C="foo",COUNTIFS(ROW(C:C),"<="&ROW(C:C),C:C,"foo"),""))
https://docs.google.com/spreadsheets/d/1ckKTsbJ7xV0SCA7tUZ8J2hB30YpTByIBPDk9OKBE_AU/edit?usp=sharing
Solution
use:
=ARRAYFORMULA(ARRAY_CONSTRAIN({IF(B2:B="", COUNTIFS(
VLOOKUP(ROW(B2:B), IF(B2:B<>"", {ROW(B2:B), B2:B}), 2, 1),
VLOOKUP(ROW(B2:B), IF(B2:B<>"", {ROW(B2:B), B2:B}), 2, 1),
ROW(B2:B), "<="&ROW(B2:B))-1, B2:B), C2:C},
MAX((B2:B<>"")*(ROW(B2:B)))-1, 2))
- Remove first element from a $_POST array
- Trouble reading numbers from file into array
- Dynamically populate a subarray inside of an array declaration
- Only print array row data if id is found in a whitelist array
- Increment variable name in a loop
- Transpose an associative 2d array
- how to split each word in a string in a separate list
- Array is always empty even when it is expected to have elements
- Build a multi-line address string with conditional components
- Access only the first row of a 2d array
- Doubled elements in result array while scraping HTML content
- Loop over an array of unknown depth
- How to access keys and values of a multidimensional array
- How to union ranges in google spreadsheets
- Validate an array as "empty" if it has no elements or if all elements have no length
- Generate an array of href strings from all <a> tags in an HTML document using Simple HTML DOM Parser
- Extracting JSON response and putting them inside a customize JSON
- Best way to filter an objects's properties for data
- How to loop over json-encoded data?
- Fill array with numbers adding up to a nominated total without exceeding item maximum
- Generate an associative 2d array with incremented column values using a flat array as first level keys
- Put Values in Multidimensional Array keys
- What is the proper way to initialize a fixed length array?
- Need help understanding pointer to a type of n elements in C (int (*p)[10])?
- Create Pivot table and add additional columns from another dataframe
- Algorithm to reverse an array/string only in terms of rotate operations
- Group data from lines of a log file by one column and create subarrays from another column
- When did iterating on the span of an array become faster than iterating on the array itself
- Use values of a flat array as the keys in every row of a 2d array
- Function to clear any value by assigning to it a default constructed value of same type