Count Unique with Criteria

I converted to a range because it's easier to illustrate the properties of the arrays:

The formula:
=SUM(IF(FREQUENCY(IF('My Solution'!$A$2:$A$28=$I$1,IF('My Solution'!$B$2:$B$28=J$1,IF('My Solution'!$C$2:$C$28=$K$1,IF('My Solution'!$D$2:$D$28=$H4,MATCH('My Solution'!$E$2:$E$28,'My Solution'!$E$2:$E$28,0))))),ROW('My Solution'!$E$2:$E$28)-ROW($E$2)+1),1))

We'll start with MATCH.
When the lookup value and the array are the same, MATCH outputs an array of values that indicate where it found each.
A position number will be repeated when duplicates are found because it finds the first match with each iterative lookup.

For example MATCH({1;2;1;2;1;2},{2;1;5;5;2;1},0) = {2;1;2;1;2;1} because we were looking for 1 found it in the 2nd position, then looked up 2 and found it in the first position, and then repeated. It never even found the 2 or 1 in the 5th and 6th positions because it didn't get that far before it found a match.

MATCH('My Solution'!$E$2:$E$28,'My Solution'!$E$2:$E$28,0) = {1;2;3;4;5;4;3;1;9;10;5;1;13;13;15;16;17;18;19;20;21;22;23;5;5;3;1}

The first lookup was found on row 1. Notice that lookup # 8 was also found on row 1. It didn't match itself to the 8th position.
The output of the match highlights which values of the range are the same; their actual values are irrelevant.

---

IF
When the tested condition is true, the output value is substituted for the value of TRUE. If no FALSE output is provided, the default output for FALSE tests is FALSE.
=IF({1;2;3}>1,"BOB") = {FALSE;"BOB";"BOB"}

Background information: Any number that's not 0 is TRUE.

=IF({-1;0;1},{4;5;6}) = {4;FALSE;6}

---

Let's evaluate:
=IF('My Solution'!$A$2:$A$28=$I$1,IF('My Solution'!$B$2:$B$28=J$1,IF('My Solution'!$C$2:$C$28=$K$1,IF('My Solution'!$D$2:$D$28=$H4,MATCH('My Solution'!$E$2:$E$28,'My Solution'!$E$2:$E$28,0)))))
=IF('My Solution'!$A$2:$A$28=$I$1,IF('My Solution'!$B$2:$B$28=J$1,IF('My Solution'!$C$2:$C$28=$K$1,IF('My Solution'!$D$2:$D$28=$H4,{1;2;3;4;5;4;3;1;9;10;5;1;13;13;15;16;17;18;19;20;21;22;23;5;5;3;1}))))
=IF('My Solution'!$A$2:$A$28=$I$1,IF('My Solution'!$B$2:$B$28=J$1,IF('My Solution'!$C$2:$C$28=$K$1,IF({FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE},
{1;2;3;4;5;4;3;1;9;10;5;1;13;13;15;16;17;18;19;20;21;22;23;5;5;3;1}))))

Now, if the test came out true, the true value is substituted.
=IF('My Solution'!$A$2:$A$28=$I$1,IF('My Solution'!$B$2:$B$28=J$1,IF('My Solution'!$C$2:$C$28=$K$1,{FALSE;FALSE;3;FALSE;FALSE;FALSE;3;1;FALSE;FALSE;FALSE;1;FALSE;FALSE;15;FALSE;17;FALSE;FALSE;FALSE;21;22;23;FALSE;FALSE;3;1})))

And again working outwards:
=IF('My Solution'!$A$2:$A$28=$I$1,IF('My Solution'!$B$2:$B$28=J$1,IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE},{FALSE;FALSE;3;FALSE;FALSE;FALSE;3;1;FALSE;FALSE;FALSE;1;FALSE;FALSE;15;FALSE;17;FALSE;FALSE;FALSE;21;22;23;FALSE;FALSE;3;1})))

=IF('My Solution'!$A$2:$A$28=$I$1,IF('My Solution'!$B$2:$B$28=J$1,{FALSE;FALSE;3;FALSE;FALSE;FALSE;3;1;FALSE;FALSE;FALSE;1;FALSE;FALSE;15;FALSE;17;FALSE;FALSE;FALSE;21;22;23;FALSE;FALSE;3;1}))

And again..:
=IF('My Solution'!$A$2:$A$28=$I$1,IF({TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE},{FALSE;FALSE;3;FALSE;FALSE;FALSE;3;1;FALSE;FALSE;FALSE;1;FALSE;FALSE;15;FALSE;17;FALSE;FALSE;FALSE;21;22;23;FALSE;FALSE;3;1}))

And again:
=IF('My Solution'!$A$2:$A$28=$I$1,{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;1;FALSE;FALSE;FALSE;1;FALSE;FALSE;15;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;22;FALSE;FALSE;FALSE;FALSE;1})

=IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;1;FALSE;FALSE;FALSE;1;FALSE;FALSE;15;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;22;FALSE;FALSE;FALSE;FALSE;1})

Final Output of IFs and MATCH:
={FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;1;FALSE;FALSE;FALSE;1;FALSE;FALSE;15;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

These are where in the range all criteria were met. Duplicates of those values have the same number.

---

ROW
In its simplest form, this merely returns the ROW number of the reference used.
In complex formulas that use array logic, you'll often find yourself wanting to push multiple criteria or lookups etc through a single function simultaneously, or have a need to generate a large number of sequential values. While you could type out {1;2;3;4;5;6} you can also just use ROW(a1:A6); the result is the same.

ROW('My Solution'!$E$2:$E$28)-ROW($E$2)+1 is just a complicated way of saying "Taking the same number of rows used in my reference, adjust them so that the given values start at 1." The actual reference is used so that if you inserted 500 rows in the middle, the ROW calculation would likewise adjust itself.

Assuming your data is static, these formulas all mean the same thing:
ROW(A1:A27)
ROW('My Solution'!$E$2:$E$28)-ROW($E$2)+1
ROW('My Solution'!$E$2:$E$28)-1

They are simply a very short way to write out 1-27.

---

FREQUENCY
Frequency counts how many values in a range are <= a given value. FREQUENCY({1;2;3},2) = 2 because 1 and 2 <= 2.
Used with another range value, it counts how many values are between them.
FREQUENCY({1;2;3},{2;3}) = {2;1} because two values are less than or equal to 2. One value is greater than 2 and less than or equal to 3.

Let's evaluated the output of our IF MATCH and ROWs

FREQUENCY({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;1;FALSE;FALSE;FALSE;1;FALSE;FALSE;15;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27})

How many of the first range are <=1? The answer is two because there are two 1's.
How many of the first range are >1 and <=2? None.
How many of the first range are >2 and <=3? None.
...
...
...
How many of the first range are >14 and <=15? There is one value that meets that criteria.
...
...
How many of the first range are >26 and <=27? None.

{2;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0}

Now we go back to another IF. IF({2;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0},1)

For each value, IF something is not zero, then it is true. The TRUE output in this formula is 1.

{1;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;1;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

Now we SUM these values. The FALSE values are ignored. The answer is 2.

---

Reviewing the formula:

1. Of the 4 consecutive IF formulas, only 3 rows passed all conditions
2. These rows MATCH labeled them as something in row 1, something in row 8 that was the same as row 1, and something in row 15
3. Frequency dumped these values into 27 buckets; the first two went into the same bucket and the last went into another
4. The outside IF got rid of the unused buckets
5. The SUM counted up 2 buckets used

tl;dr Highlight function, ranges, and expressions and Hit F9 to see what's going on a little bit at a time.