Any way to get a SUMPRODUCT formula for non-contiguous cells?

[TomCon]

What i would like is Sumproduct of array1= A1,A3. Array2 = B1, B3.

=SUMPRODUCT((A1,A3),(B1,B3)) gives #VALUE . If you try to use {}, SUMPRODUCT will only accept constant numbers, not cell references.

When i type the formula with the (), the formula tip seems to suggest it is working. For instance, when i type
"=SUMPRODUCT((A1,A3)," after typing the comma it highlights array2, suggesting to me it accepted the expression "(A1,A3)" as array1...yet still final result is #VALUE .

Is it possible?

Thanks!

 

[Joe4]

Another way

=SUMPRODUCT(A1:A3,C1:C3, --(MOD(ROW(A1:A3),2)=1))

M.

Yes, I was thinking something along those lines too, which is why I asked if there was any sort of pattern in his actual range (like it was only the odd rows, in which that formula you just posted would work for bigger ranges too that met that criteria).

I have the feeling that he was oversimplying the data in the question, for example purposes, and wonder that the real ranges look like (if that is indeed true).

 

[Marcelo Branco]

Yes, I was thinking something along those lines too, which is why I asked if there was any sort of pattern in his actual range (like it was only the odd rows, in which that formula you just posted would work for bigger ranges too that met that criteria).

I have the feeling that he was oversimplying the data in the question, for example purposes, and wonder that the real ranges look like (if that is indeed true).

Supposing the rows do not follow a pattern, for example: rows 1, 3, 7, 9, 14.

An alternative would be to create a named range, say MyRows, containing the rows of interest and use something like this:
=SUMPRODUCT (A1:A14,C1:C14, --ISNUMBER (MATCH(ROW (A1:A14),MyRows,0)))

M.

 

[TomCon]

It is true that i somewhat simplified the problem. The example i gave had array1 and array2 having 2 entries each. Actually in my current situation they have 3 entries each. However, i wanted to ask the question as fully general, that is, is there a way to have n entries in each array argument to SUMPRODUCT, where the array items are not contiguous. Also in one case, one of the array entries i want to use is a value within a PivotTable.

It turns out that the form proposed above with CHOOSE does work. It works for non-contiguous arrays of 2 or 3 entries (as far as i tested, but it seems like it would be generalizable to n entries). It also works if one of the entries can be, instead of a cell reference like A3, etc, it can be a GETPIVTODATA() call. So, this does solve my problem. So, it seems like the "trick" is to use CHOOSE, and an array like {1;2;3;...;n} as the first argument to choose, followed by the non-contiguous cells you need to have included in the SUMPRODUCT.

Is it easy to explain why this works? It never occurred to me that CHOOSE with such an array argument would have this behavior. But, thanks! Sometimes I have to use a solution that works, even if i do not fully understand it!