Spill operator error when using sumproduct

[jazzista]

Hello Forum.
I want to understand why when using the # spill operator wrapped inside sumproduct the formula returns an #N/A. I am attaching the spread with 2 formulas: One formula is using cell ranges with sumproduct (it works ) and the other one with the # operator and the N/A error( Highlighted in yellow)

Any help is greatly appreciate it . Thanks in advance

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[Domenic]

That's becuase F3# refers to the spilled array F3:F8. It's not referring to the single cell F3. So the formula would be equivalent to the following...

=SUMPRODUCT(($B$2:$B$435=F3:F8)*1)

...which is not what you want. As a result, since the ranges are not the same size, it returns #N/A. You can, however, use the implicit operator...

G3, copied down:

=SUMPRODUCT(($B$2:$B$435=@$F$3#)*1)

You'll notice, though, the formula will return #VALUE! when it's not entered on a row that corresponds with a cell within the spilled array.

 

[Momentman]

@Domenic : I was just typing when i saw your post, so i had to halt 'cos this is your domain
The Spill operator is not the problem but the dimensions of the ranges being compared

Maybe he could also use a

COUNTIF( $B$2:$B$435 ,F3#)

 

[Domenic]

@Domenic : I was just typing when i saw your post, so i had to halt 'cos this is your domain
The Spill operator is not the problem but the dimensions of the ranges being compared

Maybe he could also use a

COUNTIF( $B$2:$B$435 ,F3#)

Oh yes, most definitely... Your formula will return a spilled array with the same results.

Cheers!

 

[Peter_SSs]

Maybe he could also use a

COUNTIF( $B$2:$B$435 ,F3#)

.. and since that spills, there is in fact no need for any absolute addressing either (not that it will do any harm of course).

=COUNTIF(B2:B435,F3#)

Same thing for the original SORT(UNIQUE(.... formula
=SORT(UNIQUE(B2:B433))

 

[Peter_SSs]

.. and given that the lengths of the spills will be unknown until they happen, perhaps place the sum differently and use this instead of having to manually adjust the SUM range.

spill array and sumproduct.xlsx
	F	G
1		432
2	Summary	Totals
3	Injecting	4
4	Permanently Abandoned	2
5	Plugged and Abandoned	1
6	Producing	317
7	Shut In	66
8	Temporarily Abandoned	42
9
Sheet1

Cell Formulas
Range	Formula
G1	G1	=SUM(G3#)
F3:F8	F3	=SORT(UNIQUE(B2:B433))
G3:G8	G3	=COUNTIF(B2:B435,F3#)
Dynamic array formulas.

 

[jazzista]

That's becuase F3# refers to the spilled array F3:F8. It's not referring to the single cell F3. So the formula would be equivalent to the following...

=SUMPRODUCT(($B$2:$B$435=F3:F8)*1)

...which is not what you want. As a result, since the ranges are not the same size, it returns #N/A. You can, however, use the implicit operator...

G3, copied down:

=SUMPRODUCT(($B$2:$B$435=@$F$3#)*1)

You'll notice, though, the formula will return #VALUE! when it's not entered on a row that corresponds with a cell within the spilled array.

Hi Domenic: Good morning. Thanks you very much for your solution and explanation about the N/A error. I made a small change in your formula you provided me which is : =SUMPRODUCT(($B$2:$B$435=@F$3#)*1) . I am just locking the row 3 instead of locking the whole cell , otherwise it would return N/A.
So in order to avoid the N/A, i should always use the "@" (implicit operator) with the spill operator "#". ? Again, thank you very much.

 

[jazzista]

@Domenic : I was just typing when i saw your post, so i had to halt 'cos this is your domain
The Spill operator is not the problem but the dimensions of the ranges being compared

Maybe he could also use a

COUNTIF( $B$2:$B$435 ,F3#)

Hi Momentman: Thanks for your solution. I was trying to see why i would always get an error when using Sumproduct with any of the new excel formulas that would return spill results . Also, thanks for clarifying the dimension of the ranges being y problem ( completely forgot the ranges being return)

 

[jazzista]

.. and since that spills, there is in fact no need for any absolute addressing either (not that it will do any harm of course).

=COUNTIF(B2:B435,F3#)

Same thing for the original SORT(UNIQUE(.... formula
=SORT(UNIQUE(B2:B433))

HI Peter_SSs : Good morning. Thanks for your post to my question and elegant solution

 

[jazzista]

.. and given that the lengths of the spills will be unknown until they happen, perhaps place the sum differently and use this instead of having to manually adjust the SUM range.

Thanks for your SUM (G3#) solution. Very clever.