Excel and L1-norms

In an Excel spreadsheet I am studying (actually generating an optimization model from the formulas in the spreadsheet) I see the following construct:

=SUMIF(X,">0")-SUMIF(X,"<0")

where X is a range. This looks like a strange construct. It is meant to implement the sum of absolute values\[||x||_1=\sum_i |x_i|\] also known as the L1-norm. The L2-norm \[||x||_2 = \sqrt{\sum_i x_i^2}\] is easy to do in Excel:

=SQRT(SUMSQ(X))

Strangely, there is no obvious (that is, obvious to me) formula to do the L1-norm. Excel is a large program, with tons of options: there is almost always some workaround available. In [1] a list is provided.

The  L\(\infty\)-norm defined by\[||x||_{\infty}=\max_i |x_i|\]has similar issues: there is no built-in formula for this.

Here is summary:

The =SUM(ABS(X)) and =MAX(ABS(X)) formulas do not give what we want. It is somewhat surprising that =SUMPRODUCT(ABS(X)) is doing the right thing (although the name of the function is now misleading: we dropped the PRODUCT part of the functionality). The array formulas [2] seem to be most intuitive.

References

1. How to Sum the Absolute Values in Excel? https://www.extendoffice.com/documents/excel/1287-excel-sum-absolute-value.html
2. Guidelines and Examples of Array Formulas, https://support.office.com/en-us/article/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7