They are indeed very powerful, if unfortunately named, and the UI always takes me a while to do it properly in Excel.

I like how MacOS Numbers does it, the Categories feature is much easier to use, however it has limitations that are very annoying if you reach them.

For anything complex these days, it is often easiest to export the spreadsheet to CSV and run SQL queries on it with CSVQ.