My apologies if this is the rediscovery of the wheel. Maybe it will help someone all the same though.
I needed a way, using XLOOKUP, to return the sum of a range and all of the examples I came across were of the same type as shown in the 'Example 5' worksheet which use a date to establish the brackets for the sum function. Unfortunately my workbook doesn't have dates, or any other information, that could be easily used to establish my brackets.
What I stumbled upon is what's in 'Sheet 1'. The structure is the same as Example 5 but the XLOOKUP function is duplicated EXCEPT the search mode of the second XLOOKUP is set to -1.
You can achieve this with Power Query or if you have O365 the GroupBy Function
Power Query
let
Source = Excel.CurrentWorkbook(){[Name="tbl_test"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Name"}, {{"total qty", each List.Sum([Quantity]), type number}})
in
#"Grouped Rows"
With Groupby here is the formula
=GROUPBY(tbl_test[[#All],[Name]],tbl_test[[#All],[Quantity]],SUM,3)
Hello,
Just sharing my thoughts here, all solutions leading to correct data are good solutions. Easier though to use the SUMIF(S) function for this kind of scenario, or you can stick with SUM.
=SUM((tbl_test[Quantity])*(tbl_test[Name]=E4))
Br,
Anders
