Sumif (without the s) worked in Excel 4 (1992), you just had to enter it with CTRL+Enter and use curly braces IIRC. I used it to build dashboards. Not sure what problem sumifs solves beyond sumif.
XLookup sure is useful but again you can probably replace it with a combination of vlookup and hlookup (or index match).
Regarding the size... If you're dealing with huge spreadsheets it's really better to use a proper db. Or even manipulate data with sqlite. sqlite can query xlsx files directly (with an extension), it's extremely fast and stable.
The problem with the column limit is not so much about huge datasets as limiting flexibility in how you can lay things out - 65k rows and 16.7m cells are plenty and I’d be wanting to use a database well before I got there. But 255 columns does feel quite constraining.
And whilst you can work around lack of XLOOKUP or SUMIFS using the older functions, again it constrains how you lay things out (eg VLOOKUP needs you to presort your table by the lookup column if you don’t want an exact match) and this can often result in sheets which are much more unwieldy and slow to calculate.
XLookup sure is useful but again you can probably replace it with a combination of vlookup and hlookup (or index match).
Regarding the size... If you're dealing with huge spreadsheets it's really better to use a proper db. Or even manipulate data with sqlite. sqlite can query xlsx files directly (with an extension), it's extremely fast and stable.