Summing values based on a sequence of dates and a category
This is about making a weekly based Liquidity calculation, that lists the costs for each expense based on a category followed by a sequence of dates.
I asked a question before; Fetching information from multiple columns based on value on the same row in Google Sheets
That was about the same document but with just names and not categories for the expenses. After that a decision was made that the summing should be based on a category in order to only have the necessary information on the Liquidity calculation.
I've managed to edit the formula that was given to me by @rockinfreakshow to fit all the purposes I have but now I'm stuck with this one.
The structure where the dates are located is the same, only now with the category column added:
| Column A | Column B | Column A | Column B |
|---|---|---|---|
| Category 1 | Expense 1 | Amount | Dates-> |
| Category 2 | Expense 2 | Amount | Dates-> |
The structure of Liquidity view stays the same with the top of the tab having date&week&month&year and first column with the category to sum:
| Column A | Date | <- + 7 days | <- + 7 days |
|---|---|---|---|
| Category 1 | sum | sum | sum |
| Category 2 | sum | sum | sum |
The formula that was suggested before
=map(A5:index(A:A,match(,0/(A:A\<\>""))),lambda(Σ,if(Σ="",,index(if(ifna(xmatch(B4:4,xlookup(torow(xlookup(Σ,'Recurring Costs'!A:A,'Recurring Costs'!C:ZZ),1),4:4,4:4,,-1))),vlookup(Σ,'Recurring Costs'!A:B,2,),)))))
I've tried to replace the vlookup with filter, but that sums all the expenses for the category and places them according to the first sequence of dates the category has, and I haven't succeeded in trying to specify the wanted values within filter. I've experimented with many different edits and formulas but no luck so far.
Here is the example sheet, including the previous question and answer for it:
https://docs.google.com/spreadsheets/d/18sX800vqc4YsijSvzEfOf27M16uUkrD9bibYIvFrIew/edit?usp=sharing
You may try:
=map(A14:A16,lambda(Σ,map(B4:4,lambda(Λ,if(Λ="",,let(Ξ,sum(ifna(filter('Recurring Costs'!C2:C7,'Recurring Costs'!A2:A7=Σ,let(Δ,xlookup('Recurring Costs'!D2:H7,4:4,4:4,,-1),byrow(Δ,lambda(Γ,ifna(xmatch(Λ,Γ)))))))),if(Ξ=0,,Ξ)))))))
- Conditional Dropdowns in Google Sheets Based on Multiple Criteria
- How to use Apps script to move a lot of data rows at same time to different spreadsheet automatically base on time
- How to ignore empty rows when submitting to database
- Custom Menus Google Apps Script
- Google Sheets Index Sidebar of all tabs - add search functionality
- How do I compare new IDs with a column of existing IDs and allowcate a timestamp if two IDs match on Google Sheets?
- Google script Display array with rows of different sizes
- List rows in array when the cell contain number in the string
- ImportXML XPath issue using Google Sheets on a simple web scraping query
- Return index of cell that contains match on two strings
- Conditional formatting if cell past date and another cell empty with arrayformula
- How to find the Second Space from the right in Google Sheets?
- Joining more than two WHERE statements in Query Language
- Python gspread create sheet if not exist
- Find if cell contents exist in array
- Google Apps Script: Finding an element in a Column and returning the corresponding row
- Active VS Effective User for Google Sheets
- Rearrange data in table format to rows
- I am not able to extract URL from these cell values using Google Apps Script
- How to count a specific word from a string in a cell from the entire column in google sheets
- Error returned by action script when calling an API with UrlFetchApp.fetch
- How can I make this formula more dynamic by including another criteria (cell F11) in the query?
- How to execute a Google Apps Script ONLY on the edited row
- How to copy the last row in one table and append to another table in the same GSheet (the same tab)
- Replace duplicate row with new data in Google Sheets using Apps Script
- In Google sheets, how does one either return a blank cell or perform a function based on another cells lack of content?
- Incorrect result of iferror formula via Apps Script
- How to Extract URLs from HYPERLINK in Google Sheets
- Using a reference cell as part of a column in a google sheets query
- Can a formula return corresponding values from one column into another, removing blanks and separating them with commas?