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

 

[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.

I Hope Mr excel ( Bill Jelen) could do a video in youtube on this topic clarifying this N/A using the implicit operator in SUMPRODUCT.

 

[Domenic]

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.

Based on your original data, it shouldn't make a difference. The formula shouldn't return #N/A when the reference to both the row and column are absolute.

So in order to avoid the N/A, i should always use the "@" (implicit operator) with the spill operator "#". ?

You use the implicit intersection operator when the value returned is a range and you want to return a single value from a cell on the same row or column as the formula. For example...

F3# refers to the spilled range, which in this case is F3:F8.

@F3# refers to the cell from the spilled range that occurs, in this case, on the same row as the formula.

However, since you're using dynamic array Excel, you might as well take advantage of it and use a dynamic array formula to return a spilled array, like both @Momentman and @Peter_SSs have shown you...

=COUNTIF(B2:B435,F3#)

Cheers!

 

[jazzista]

Based on your original data, it shouldn't make a difference. The formula shouldn't return #N/A when the reference to both the row and column are absolute.



You use the implicit intersection operator when the value returned is a range and you want to return a single value from a cell on the same row or column as the formula. For example...

F3# refers to the spilled range, which in this case is F3:F8.

@F3# refers to the cell from the spilled range that occurs, in this case, on the same row as the formula.

However, since you're using dynamic array Excel, you might as well take advantage of it and use a dynamic array formula to return a spilled array, like both @Momentman and @Peter_SSs have shown you...

=COUNTIF(B2:B435,F3#)

Cheers!

Thanks Donenic for the explanation and to Peter_SSs & Momentman as well for their elegant solutions. Appreciate the help on this forum. Regards

 

[Peter_SSs]

Thanks Donenic ... and to Peter_SSs & Momentman as well for their elegant solutions. Appreciate the help on this forum.

You're welcome. Glad we could help. Thanks for the follow-up.