&"" in array SUMIF

[cleverclog]

Hello, I have found a formula that returns distinct values excluding blanks, but I do not understand what the role of &"" is.

The formula is:
[FONT=&quot]=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10[/FONT][FONT=&quot]&""[/FONT][FONT=&quot]) + IF($A$2:$A$10="",1,0), 0)), "")
The formula returns the same results whether the ampersand is used or not, however when evaluating the formula you can see that it takes one step more to get the result.

Could anyone please tell me what the role of &"" in the formula is and how it can be used in other cases?

Thanks in advance [/FONT]

 

[Jonmo1]

Welcome to the board..

If it works without it (you're sure it produces the correct result?) then just remove it.

It looks like the reason for it to be there would be if the values in B are "Numbers Stored As Text", but the values in A are real numbers.

 

[MySpreadsheetLab]

Could &"" be used to convert values to text?

 

[cleverclog]

Yes, I am sure it produces the same results, but since I am not the author of the formula, I wanted to know whether or how it can be used in other formulas. If &"" is used in evaluating the formula you can see an array of values the formula chooses from and then an array of 0s and 1s. If you don't use &"" in the formula, there is no array of values to choose from and you can only see an array of 0s and 1s in the evaluation.

 

[barry houdini]

It seems you don't need it here but it changes the results when blanks are present. When you use this version

=COUNTIF($B$1:B1, $A$2:$A$10)

and all referenced cells are empty it evaluates as follows

{0;0;0;0;0;0;0;0;0}

Change to this version

=COUNTIF($B$1:B1, $A$2:$A$10&"")

and you get

{1;1;1;1;1;1;1;1;1}<sub><sup><strike>


</strike></sup></sub>Often it's used when you are dividing by the COUNTIF array (to avoid a #DIV/0! error) but that isn't the case here​

 

[Jonmo1]

After further testing, the countif is not hampered by the 'numbers stored as text' issue in column A like I thought it might be.
So it appears the &"" has no purpose (that I can tell).

Maybe as the sheet's new owner, the more important question to consider...
What is the formula 'supposed to actually do' ?
Is there a simpler way? (i would probably say Yes, but I can't tell what the purpose of the formula is).

 

[cleverclog]

It seems you don't need it here but it changes the results when blanks are present. When you use this version

=COUNTIF($B$1:B1, $A$2:$A$10)

and all referenced cells are empty it evaluates as follows

{0;0;0;0;0;0;0;0;0}

Change to this version

=COUNTIF($B$1:B1, $A$2:$A$10&"")

and you get

{1;1;1;1;1;1;1;1;1}<sub><sup><strike>


</strike></sup></sub>Often it's used when you are dividing by the COUNTIF array (to avoid a #DIV/0! error) but that isn't the case here​

Well, I have tested the formula in my file and the list I am using as a source of values contains blanks, and still the arrays contain exactly the same combinations of 0s and 1s, so I don;t think this is the case. I can upload the file but I use Polish Excel and I am not sure whether the functions will be converted into English.

 

[cleverclog]

You can find the file at https://ufile.io/wjfue

 

[Jonmo1]

Ok the formulas purpose is to create a unique list from a list containing duplucates and blanks.

The &"" is definately not needed.
It's as barry explained, changing array of {0,0,0,0,0,0} to {1,1,1,1,1,1}
You'll see it if A is all blank AND E1 is blank (but you'll only see it in the formula in E2)

Empty A2:A10 AND E1
Formula in E2 with the &"" gives the array of all 1's from the countif function
Remove the &"" from E2, and you get all 0's from the countif function

The purpose of this would be to avoid a #Div/0 error when using the array in a division function.
But that's not the case here.