I want to apply, to a whole column, a formula that is already an arrayformula
I have a ledger with a bunch of expenses listed by date, category, and amount. Expenses ledger
Separately I have an analytics sheet for one category, "Gas". I want the analytics sheet to show total gas expenditures month by month without having to update anything other than the ledger.
I can get a list of unique months with this formula:
=UNIQUE(ARRAYFORMULA(DATE(YEAR('Main Ledger'!A2:A), MONTH('Main Ledger'!A2:A), 1)))
This populates the months the way I want, and the list will automatically grow with the ledger. I put these in column A.
For each month, I want to compare every entry in the ledger against the date in column A and the category "Gas". I can do that for a single month with the following formula in column B:
=ArrayFormula(SUMIFS('Main Ledger'!D$2:D,MONTH('Main Ledger'!A$2:A),MONTH(A2),YEAR('Main Ledger'!A$2:A),YEAR(A2),'Main Ledger'!F$2:F,"Gas"))
Because I have to use arrayformula to make the date comparison on the ledger, I'm having trouble figuring out how to then use arrayformula to automatically apply this monster formula to all of column B. As column A grows (automatically because of UNIQUE, I want column B to grow too.
I do not want to prepopulate column B and have a bunch of zeroes lying around, mainly because I will be using columns A and B to populate a chart.
try in one go:
=QUERY({Sheet1!A2:D},
"select month(Col1),sum(Col4)
where Col4 is not null
group by month(Col1)
label month(Col1)'month'")
update:
=INDEX(ARRAY_CONSTRAIN(QUERY({TEXT(Sheet1!A2:A, "mmmm"), Sheet1!A2:D},
"select Col1,sum(Col5),month(Col2)
where Col5 is not null
group by month(Col2),Col1
label Col1'month'"), 9^9, 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