Frequency Function Anomaly

[SkipVought]

Today there was a post, Distinct Count With Multiple Qualifiers.

The Data example was
[pre]
Tote # MHE Translated
Tote 1 TWR1EXT2
Tote 1 TWR1EXT2
Tote 1 TWR1EXT2
Tote 2 Location B
Tote 2 Location B
Carton 1 TWR1EXT2
[/pre]

The user wanted the unique count of Tote # and MHE Translated, where MHE Translated equals "TWR1EXT2", which should be 2.

I thought I could use the Frequency() function in Sumproduct(), first by adding a helper column...
[pre]
Tote # MHE Translated Tote & MHE
Tote 1 TWR1EXT2 Tote 1TWR1EXT2
Tote 1 TWR1EXT2 Tote 1TWR1EXT2
Tote 1 TWR1EXT2 Tote 1TWR1EXT2
Tote 2 Location B Tote 2Location B
Tote 2 Location B Tote 2Location B
Carton 1 TWR1EXT2 Carton 1TWR1EXT2
[/pre]

[tt]
=SUMPRODUCT((Table_ExternalData_4[MHE Translated]="TWR1EXT2")*(FREQUENCY(MATCH(Table_ExternalData_4[Tote & MHE],Table_ExternalData_4[Tote & MHE],0),MATCH(Table_ExternalData_4[Tote & MHE],Table_ExternalData_4[Tote & MHE],0))>0))
[/tt]

But the result is #N/A!

So if use a technique to analyzing formulas, especially array formulas: where in the formula bar you SELECT a part of the expression, Hit F9 and the resolved value is displayed, and FINALLY hit the ESC Key to return the expression, else you loose that part of the formula.

So Selecting [highlight #FCE94F]this[/highlight]
[tt]
=SUMPRODUCT([highlight #FCE94F](Table_ExternalData_4[MHE Translated]="TWR1EXT2")[/highlight]*(FREQUENCY(MATCH(Table_ExternalData_4[Tote & MHE],Table_ExternalData_4[Tote & MHE],0),MATCH(Table_ExternalData_4[Tote & MHE],Table_ExternalData_4[Tote & MHE],0))>0))
[/tt]

...produces
[tt]
=SUMPRODUCT([highlight #FCE94F]{TRUE;TRUE;TRUE;FALSE;FALSE;TRUE}[/highlight]*(FREQUENCY(MATCH(Table_ExternalData_4[Tote & MHE],Table_ExternalData_4[Tote & MHE],0),MATCH(Table_ExternalData_4[Tote & MHE],Table_ExternalData_4[Tote & MHE],0))>0))
[/tt]

...which is what I would expect.

And now the kicker!

Selecting [highlight #FCE94F]this[/highlight]
[tt]
=SUMPRODUCT([highlight #FCE94F](Table_ExternalData_4[MHE Translated]="TWR1EXT2")[/highlight]*[highlight #FCE94F](FREQUENCY(MATCH(Table_ExternalData_4[Tote & MHE],Table_ExternalData_4[Tote & MHE],0),MATCH(Table_ExternalData_4[Tote & MHE],Table_ExternalData_4[Tote & MHE],0))>0)[/highlight])
[/tt]

...produces
[tt]
=SUMPRODUCT([highlight #FCE94F](Table_ExternalData_4[MHE Translated]="TWR1EXT2")[/highlight]*[highlight #FCE94F]{TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE}[/highlight])
[/tt]

Yes! It returns NOT SIX, but SEVEN TRUE/FALSE elements in the array!!!.

Each MATCH() returns 6 values in the array, but the FREQUENCY function returns {3;0;0;2;0;1;0}, Seven elements!!!!

So in my SUMPRODUCT my two array products are mismatched
[pre]
TRUE TRUE = 1
TRUE FALSE = 0
TRUE FALSE = 0
FALSE TRUE = 0
FALSE FALSE = 0
TRUE TRUE = 1
FALSE = #N/A!
[/pre]
... hence the #N/A! But as you see, if it were not for the #N/A! mismatch, the result would be 2.

What's happening?

Now I tried a trick that does not sit well with me. I changed the first range to A1 notation and added a bogus row by changing B7 to B8...
[tt]
=SUMPRODUCT(([highlight #E9B96E]B2:B8[/highlight]="TWR1EXT2")*(FREQUENCY(MATCH(tSRC[Tote & MHE],tSRC[Tote & MHE],0),MATCH(tSRC[Tote & MHE],tSRC[Tote & MHE],0))>0))
[/tt]
...and it returned 2 as expected. But REALLY????

 

[combo]

It's OK. MATCH returns array {1;1;1;4;4;6}. FREQUENCY returns array that is one cell bigger than it's second argument size (it's number of divisors, the 7th 0 is number of items above 6).

combo