0

SUMPRODUCT({1,2,3},{4,5,6}) gives 32, which is obtained by multiplication of the element in one array by the corresponding element in the other array.

The following two formulae give two arrays of 2880 elements each, as evidenced by evaluation of the formulae (F9). I copied the evaluation of the first formula and pasted it by text-to-column to one row. I then copied the evaluation of the second formula and pasted it to the row below. The summation of the multiplication of the top row by the lower row gives 16, which is the correct result.

I put the two formulae in SUMPRODUCT expecting to get the same result, 16, but no, I got zero. It seems to me SUMPRODUCT doesn't multiply the two arrays or the two arrays are not what they appear to be when I evaluated them. I have to enter Ctrl-Shift-Return on the SUMPRODUCT formula to get 16.

My question is while evaluation shows two formulae are arrays why SUMPRODUCT doesn't recognize them as arrays?

=LOOKUP(ROW(INDIRECT("1:"&SUM($A1:$A10))),IFERROR(SUMIF(OFFSET($A$1,,,ROW($A1:$A10)-1),"<>")+1,1),$B1:$B3)

=LOOKUP(ROW(INDIRECT("1:"&SUM(C1:C10))),IFERROR(SUMIF(OFFSET(C$1,,,ROW(C1:C10)-1),"<>")+1,1),D1:D3)

=SUMPRODUCT(LOOKUP(ROW(INDIRECT("1:"&SUM($A1:$A10))),IFERROR(SUMIF(OFFSET($A$1,,,ROW($A1:$A10)-1),"<>")+1,1),$B1:$B3),LOOKUP(ROW(INDIRECT("1:"&SUM(C1:C10))),IFERROR(SUMIF(OFFSET(C$1,,,ROW(C1:C10)-1),"<>")+1,1),D1:D3))

 A   B    C   D
420  0  1001  0
540  1   882  1
480  0   997  0
420  0
540  1
480  0
Dominique
  • 16,450
  • 15
  • 56
  • 112
joehua
  • 725
  • 3
  • 10
  • 25
  • Have a look [here](https://stackoverflow.com/a/58536210/9758194), it might help you understand. – JvdV Dec 08 '19 at 15:21
  • Thanks. The answer says use CSE when there is IF in the formula. That's my case. – joehua Dec 09 '19 at 01:49
  • Is it correct to say that one can't trust what is seen by pressing F9? In my case, F9 gives arrays, not single value as expected by IF. – joehua Dec 09 '19 at 02:24
  • As far as I'm concerned, not really. I don't think your two formulas would have returned a true array because of implicit intersection on the lookup. – JvdV Dec 09 '19 at 05:59
  • Either way, the answer was for that case about the IF but in general it is also about implicit intersection that functions work with generally. Only few will cancel that behaviour, most don't. It wouldn't matter if they reside inside SUMPRODUCT, they require CSE. I hope I made that clear in the linked answer =) – JvdV Dec 09 '19 at 06:06

1 Answers1

0

There are two kinds of formulas in Excel: normal formulas and array-formulas.

Normal formulas are entered using the "ENTER" button, while array-formulas are entered using the "Ctrl+Shift+ENTER" key combination.

This URL gives some explanation about array-formulas.

Dominique
  • 16,450
  • 15
  • 56
  • 112
  • By the way, I added the "array-formulas" tag to your question, this gives you an idea where to look for similar questions. – Dominique Dec 08 '19 at 15:54
  • Thanks. I didn't know the existence of that tag. – joehua Dec 09 '19 at 01:38
  • I know about array formula and have use it. My question is not about general array formula. My question is specific to SUMPRODUCT, which takes arrays as argument and normally doesn't need Ctrl-Shift-Enter. However, in my case, even though I have arrays as arguments, I still need to enter Ctrl-Shift-Enter. The reference in JvdV's comment says if there are IFs in SUMPRODUCT, it needs CSE. In my case, I do have IFERROR in SUMPRODUCT. – joehua Dec 09 '19 at 01:51