So, instead of using SUMIF, I always use SUM or SUMPRODUCT using the trick that in Excel True and False are 1 and 0 when used in arithmetical operations. This is unbelievably flexible. For example, if you want to sum all the elements of column A if the element in B is between 1 and 10 then you have SUM(A1:A100 * AND(B1:B100 >=1, B1:B100 <=10)). You need to press Ctrl+Shift+Enter, as this is an array formula, but otherwise it works like a charm.

A small but important difference between this new function and SUMIF: SUMIF will sum all values in the array that match your search criteria, but this function will only return a single value.