In general your formula
=LET(
r, B1:G8,
m, 8,
a, TAKE(r, , 1),
b, INDEX(r, , 2),
c, INDEX(r, , 3),
d, INDEX(r, , 4),
e, INDEX(r, , 5),
f, INDEX(r, , 6),
g, INDEX(r, 7),
u, SORT(UNIQUE(a)),
HSTACK(u, MMULT(--(TOROW(a) = u), (MONTH(b) = m) * c))
)works, e.g.
but I was not able to paste your sample into the grid and didn't catch what exactly you try to do.
Sorry, I still can't understand how the source data is structured. Is that as
? If so what is the logic behind generating these 4 output records, why these ones not another? Or your output doesn't cover entire input?
To add screenshot here, copy it into clipboard, here click on Insert Picture button
after that click by mouse at any place in grey area
Ctrl+V, picture shall be loaded from the clipboard, when it's ready click Done at bottom right.
Back to the sample, I added headers for better understanding, in general you may ignore them.
I guess we need to sum amount for identical Id, Date, Transaction and Type. That could be done by
=LET(
range, $B$2:$G$10,
Id, CHOOSECOLS(range, 1),
date, CHOOSECOLS(range, 2),
transaction, CHOOSECOLS(range, 4),
type, CHOOSECOLS(range, 5),
amount, CHOOSECOLS(range, 6),
groupIndexes, {1,2,4,5},
group, UNIQUE( CHOOSECOLS(range, groupIndexes ) ),
VSTACK({"Id","Date","Transaction","Type","Amount"},
HSTACK(
group,
BYROW( group,
LAMBDA(v, SUM(
FILTER(amount,
(Id = INDEX(v,1,1) )*
(date = INDEX(v,1,2) )*
(transaction = INDEX(v,1,3) )*
(type = INDEX(v,1,4) )
)
) )
)
) )
)If we would like to filter for the specific year and month as they are defined here
slightly modified formula will be
=LET(
range, $B$2:$G$10,
year, $J$14,
monthNumber, $J$15,
Id, CHOOSECOLS(range, 1),
date, CHOOSECOLS(range, 2),
transaction, CHOOSECOLS(range, 4),
type, CHOOSECOLS(range, 5),
amount, CHOOSECOLS(range, 6),
monthRange, FILTER(range, EOMONTH(date,0) = EOMONTH( DATE(year,monthNumber,1), 0 )),
groupIndexes, {1,2,4,5},
group, UNIQUE( CHOOSECOLS(monthRange, groupIndexes ) ),
VSTACK({"Id","Date","Transaction","Type","Amount"},
HSTACK(
group,
BYROW( group,
LAMBDA(v, SUM(
FILTER(amount,
(Id = INDEX(v,1,1) )*
(date = INDEX(v,1,2) )*
(transaction = INDEX(v,1,3) )*
(type = INDEX(v,1,4) )
)
) )
)
) )
)Your initial formula sum amounts for the unique Id (only one column) in a given month. It's can't be adjusted directly for the few unique columns.
Another way is to create PivotTable, something like
Please check in attached file if that close to what you'd like to receive.
@Norman_McIlwaine , thank you for the feedback.
Above is just formula which you may edit directly in formula bar. If you check the file from previous post it's here
but practically not visible if you don't expand formula bar. For that click on any empty place within it and Ctrl+Shift+U. Or drag bottom border down.
Editing here use Alt+Enter to insert new line.
In addition you may install Excel Labs add-in from the Store
within it it'll be Advanced Formula Environment to work with formulae and not only.
Thank you Sergei for the valuable information, I very much appreciate it. I used the formula on the data unfortunately the amount was not summed i.e. The Personal in rows 4 and 5. I tried to review the formula only changing the year and date variables to correspond with the input in col J. The rest I could follow in a basic form however , I could not see where the calculation was taking place to give the sum amount. is it the LAMBDA I see that is were you do all the sum. However, I cannot figure it out at this time.
Thanks Sergei for all the help given.
Formula works, correctly, perhaps I misunderstood the logic. It sums the values if only each of the value in these 4 columns are the same:
For them only INSURANCE in column C is the same value, thus they are not summarised. Which exactly fields shall be the same to summarize amounts?
Sorry for the misunderstanding, in a previous image you had it right.
In a previous capture you got it just right the C and D where the same so it added the amount.
@Norman_McIlwaine Sergei, If I change the date(day) to a 1 The function works great, Is there anyway we can add in a piece of code to perform a unique when the function is finished I have tried several ways to include this into the function but have failed every time. I even wrote a separate piece of code which I got from the You Tube and adapted it to test my data prior to inserting however I get the same results frustration.
Can you please help me Sergei?
Original Data
The above formula will only work if the fields C,D,F are of the same value
Changed Original date to 2023-08-01 from 2023-08-02
The above formula works great,
If the date in column D5 are the same as column D4 2023-08-01
the Type field is then UNIQUE and the amount is summed as well.
I need the Transaction and Type fields to be unique. In other words I am trying to not have all three fields the same just two.
Thanks for all the help you have provided to me.
I removed the dates and found the formula worked well except I could not select by month. I don't need the dates at all in the output since we should will be selecting by year month.
The end result is good.
How can I select the data by Year and Month?
To exclude dates what we shall do in formula is to remove/change related indexes
This part of the formula
monthRange, FILTER(range, EOMONTH(date,0) = EOMONTH( DATE(year,monthNumber,1), 0 )),is filtering source range for the selected month, we keep it unchanged
Finally entire formula is
=LET(
range, $B$2:$G$10,
year, $J$14,
monthNumber, $J$15,
Id, CHOOSECOLS(range, 1),
date, CHOOSECOLS(range, 2),
transaction, CHOOSECOLS(range, 4),
type, CHOOSECOLS(range, 5),
amount, CHOOSECOLS(range, 6),
monthRange, FILTER(range, EOMONTH(date,0) = EOMONTH( DATE(year,monthNumber,1), 0 )),
groupIndexes, {1,4,5},
group, UNIQUE( CHOOSECOLS(monthRange, groupIndexes ) ),
VSTACK({"Id","Transaction","Type","Amount"},
HSTACK(
group,
BYROW( group,
LAMBDA(v, SUM(
FILTER(amount,
(Id = INDEX(v,1,1) )*
(transaction = INDEX(v,1,2) )*
(type = INDEX(v,1,3) )
)
) )
)
) )
)which returns
Please check in No Dates sheet attached.
I found this to be very good on the small amount of data. When I expanded the data over several months (8,4) I found the amount is summed over all months while the output from Id, Transaction, and Type are only selecting by the month requested.
Example PETRO
Accepted Solutions