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,,Ξ)))))))
