Filter function and sum totalling by column values

I've searched for similar posts to my question but hard to find answers because I keep getting links to pages that explain how to subtotal a filtered list (as in a table list) and not when using the FILTER function and so hoping someone can help.

I have a structured table called Tbl_Data (Sample also attached)

Key Cost QtyOld QtyNew QtyDiff Inclusions
AAA 1.5 10 4 6 Include
AAA 1.5 3 6 -3 Include
BBB 2.5 5 7 -2 Include
CCC 3.5 4 3 1 Include
AAA 1.5 9 4 5 Exclude
BBB 2.5 2 9 -7 Include
BBB 2.5 15 6 9 Exclude
CCC 3.5 8 2 6 Include


FILTER sample.xlsb


I need to use something other than a pivot and I'm attempting to use the filter function to output/list a subset of data where the filter will only use rows with "Include" and then aggregate or sum the total by each key.

The following formula will give me a list/table with the first, 3rd, 4th & 5th columns where [Inclusions] = "Include"

=FILTER(INDEX(Tbl_Data,SEQUENCE(ROWS(Tbl_Data)),{1,3,4,5}),Tbl_Data[Inclusions]="Include",0)

The problem I need help with is aggregating or totalling the QtyOld, QtyNew & QtyDiff by each key so as to end up with the following;

Key QtyOld QtyNew QtyDiff
AAA 13 10 3
BBB 7 16 -9
CCC 12 5 7

If that's too difficult then can I just get QtyDiff totalled by each Key ? ;
Key QtyDiff
AAA 3
BBB -9
CCC 7


Can either of these be done with Filter ?

Thanks in advance.
Gavin

Try this:

=LET(u,UNIQUE(Tbl_Data[Key]),qo,SUMIFS(Tbl_Data[QtyOld],Tbl_Data[Key],u,Tbl_Data[Inclusions],"Include"),qn,SUMIFS(Tbl_Data[QtyNew],Tbl_Data[Key],u,Tbl_Data[Inclusions],"Include"),qd,SUMIFS(Tbl_Data[QtyDiff],Tbl_Data[Key],u,Tbl_Data[Inclusions],"Include"),CHOOSE({1,2,3,4},u,qo,qn,qd))

Your forum profile is clearly way out-of-date (Excel 2003) - please update it NOW.

Or a tiny bit shorter:

=LET(u,UNIQUE(Tbl_Data[Key]),tbi,Tbl_Data[Inclusions],i,"Include",qo,SUMIFS(Tbl_Data[QtyOld],Tbl_Data[Key],u,tbi,i),qn,SUMIFS(Tbl_Data[QtyNew],Tbl_Data[Key],u,tbi,i),qd,SUMIFS(Tbl_Data[QtyDiff],Tbl_Data[Key],u,tbi,i),CHOOSE({1,2,3,4},u,qo,qn,qd))

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

Another way:

Formula:

Please Login or Register  to view this content.

I don't have Lambda available in my current version yet

Thanks for your feedback and rep .

I think you have the Semi-Annual Enterprise Channel.
If you succeed in downloading version V2208, you will also have access to the new Excel functions.

Furthermore, I expect that the regular version will be available for your Channel in mid-January 2023.

You're welcome - thanks for the rep.