### Excel Formula Challenge #7

# Challenge

Here is an interesting yet brain-bending formula challenge, Table in the screenshot shows five records, Write a formula to count the dups and unique records. notice that values in same records are not in same order every time. they are **shuffled** .

## Conditions –

- You have to solve it by excel native formulas only
- No helping columns.

Download Sample file

FormulaChalllenge #7 (56 downloads)

Try yourself first, but here are the solutions.

Solution (34 downloads)

Explanation (19 downloads)

My array formulas are:

Duplicate:

=SUM(IFERROR(1/(1/(FREQUENCY(MMULT(COUNTIF($E$8:$G$12,”<="&$E$8:$G$12),

TRANSPOSE(COLUMN($E$8:$G$8))^0),

MMULT(COUNTIF($E$8:$G$12,"<="&$E$8:$G$12),

TRANSPOSE(COLUMN($E$8:$G$8))^0))-1))+1,0))

Unique:

=SUM(–(FREQUENCY(MMULT(COUNTIF($E$8:$G$12,"<="&$E$8:$G$12),

TRANSPOSE(COLUMN($E$8:$G$8))^0),

MMULT(COUNTIF($E$8:$G$12,"<="&$E$8:$G$12),

TRANSPOSE(COLUMN($E$8:$G$8))^0))=1))

By hard-coding the array2 arguments of MMULT and using SUMPRODUCT instead of SUM in the Duplicate formula, the foregoing formulas will be reduced to these non-array versions:

Duplicate:

=SUMPRODUCT(IFERROR(1/(1/(FREQUENCY(MMULT(COUNTIF($E$8:$G$12,"<="&$E$8:$G$12),

{1;1;1}),

MMULT(COUNTIF($E$8:$G$12,"<="&$E$8:$G$12),

{1;1;1}))-1))+1,0))

Unique:

=SUM(–(FREQUENCY(MMULT(COUNTIF($E$8:$G$12,"<="&$E$8:$G$12),

{1;1;1}),

MMULT(COUNTIF($E$8:$G$12,"<="&$E$8:$G$12),

{1;1;1}))=1))

Given that the challenge requires only the use of native functions, I ensured that my formulas work for ALL VERSIONS of Excel.

Awesome formula Robert.