Excel launched two functions that emulate what PivotTables can do that are easier to write than SUMIFS. GROUPBY aggregates a value in rows and PIVOTBY aggregates values by rows and columns. Superior to PivotTables, they refresh automatically and can do more functions such as MEDIAN and my favourite ARRAYTOTEXT to create a comma-separated list of a text field: e.g.

France: Lyon, Marseille, Paris
UK: London, Bristol.

There are optional fields like headers, totals, sort, and filter plus I also walk through how to only aggregate unique values like DISTINCTCOUNT or distinct comma-separated lists, here is the formula for the latter:
=GROUPBY({Categorical column},{Text_value column},LAMBDA(x,ARRAYTOTEXT(SORT(UNIQUE(x)))))
I reference my other videos in this one:
LAMBDA:    • Excel's custom function editor: Creat...  
Dynamic array functions:    • 24 Dynamic array functions in Excel i...  
You can download the example files here: https://www.xlconsulting-asia.com/you...
Follow Solve & Excel's monthly newsletter for monthly Excel updates: https://solveandexcel.ca/excel_news/

00:00 Introduction
00:55 Group by
02:26 Group by non-adjacent columns
03:46 Groupby to make comma-separated list
04:53 Row and column pivot by with PIVOTBY
05:59 Filter
06:45 Count unique and text unique