Hi Francis,
If this were possible in Numbers, do you not think one or the other of us would have mentioned it?
OpenOffice (and LibreOffice) were designed to behave very much like MS Excel.
Numbers is not (and was obviously never intended to be) an Excel clone.
MS chose to diversify some functions (such as SUMPRODUCT) to do things beyond what their name suggests. Apple, in the case of Numbers, appears to have chosen to stay within the bounds indicated by the name of the function:
The SUMPRODUCT function returns the sum of the products of corresponding numbers in one or more collections.
IF you want to add condition(s), then you need to use one of the functions with …IF or …IFS in its name. Ian (Yellowbox) chose SUMIF and COUNTIF to get the two results you are looking for.
The collection of test values must be the same size as the collection of sum values, which doesn't permit test values taking two columns and the sum values taking only one. Ian handles that issue by doing the comparison with a formula that produces a single value ("Yes" or "No") indicating whether the pair of values in each row of columns A and B meets the condition for being included or excluded from the SUM or COUNT, making it possible to place the (new) test values in a single column, fulfilling the requirement that the ranges holding the test values and the sum values be the same size.
The extra column is needed for the calculations, but it's not necessary that this column be visible.
You've said that your actual problem is "something more complex." Often the printer's statement that 'the devil is in the details' holds for spreadsheet issues as well. Would you care to share the details of this complexity?
Regards,
Barry