Array-entered SUMPRODUCT

[juanito]

Since reading fascinating stuff on this function on Chandoo and other blogs like excelhero, I've been using it for all sorts of conditional stuff instead of other functions like IF and COUNTIF and so on.

Now SUMPRODUCT is, it seems to me, an array function that you don't need to array-enter. However, I've noticed that Excel will allow you to array-enter it, and this has been making my head explode.

Can anybody give me an example of when you would want to array-enter SUMPRODUCT? (And I don't mean when it's used in combination with another function which does require array entry)

 

[Luke M]

I can only imagine using it inside of another function that needs to be array entered, like:

=MAX(IF(A2:A100=SUMPRODUCT(something here),B1:B100))


Of course, just because you can array enter something, doesn't mean it helps...technically, you can array enter:

=1


Why you'd ever need to? =P

 

[juanito]

Thanks Luke - looks my head exploded for no good reason! (Arrays can sometimes do that, as Chandoo warns sometimes.)

 

[juanito]

Well, as it happens the mighty sam of excelhero academy fame came up with an example just the other day!


You have a range called NAME of various text values. You want to check if they're all unique or, conversely, if any is repeated. Here's how you do it (note curly brackets denote array-entry):


{=SUMPRODUCT(--(Name=TRANSPOSE(Name)))-COUNTA(Name)}


Counts the matches between each element and the entire array - if all elements are unique you get only one match per element and therefore, after you subtract the number of elements (the COUNTA part), you get zero. Any non-zero value returned means there are repeats.


I think it's the TRANSPOSE part which makes array-entry necessary - any comments?

 

[vijaySharma]

Hi John,


Yes, TRANSPOSE works on a array... here is the copy paste from Excel Online help on the same...


The TRANSPOSE function returns a vertical range of cells as a horizontal range, or vice versa. The TRANSPOSE function must be entered as an array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.) in a range that has the same number of rows and columns, respectively, as the source range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) has columns and rows. Use TRANSPOSE to shift the vertical and horizontal orientation of an array or range on a worksheet.


~VijaySharma

 

[juanito]

Thanks, Senhor Vijay - good to hear from you! (I've decided to award the star forum gurus a title... more below)


So we now have an example of array-entered SUMPRODUCT {outside} the array-required function (TRANSPOSE, in this case)... so that's a little different from Don Luke's first thoughts, although these appear fundamentally sound.


Any further thoughts welcome: full understanding of array formulas (where what you see in the spreadsheet is NOT what you get) is a key process for most of us and we appreciate the help in the forums.


- juanito

 

[vijaySharma]

Thanks John...


I can recommend visiting the ExcelIsFun page on Youtube, I dont know the name of the author; however there are pretty good videos on using complex and easy formula's there.


http://www.youtube.com/user/ExcelIsFun


Do have a look..


~VijaySharma