This example sheet has 4 columns with some random data. It is quite complicated trying to count unique distinct records from this table but Excel can help us with that.

A record is an entire row in the table below.

Array formula in cell A28:

=SUM(1/COUNTIFS($A$2:$A$25, $A$2:$A$25, $B$2:$B$25, $B$2:$B$25, $C$2:$C$25, $C$2:$C$25, $D$2:$D$25, $D$2:$D$25)) + CTRL + SHIFT + ENTER

How to create an array formula

  1. Copy (Ctrl + c) above formula
  2. Double click cell A28
  3. Paste (Ctrl + v)
  4. Press and hold Ctrl + Shift
  5. Press Enter

We can verify the calculation by extracting all unique distinct records from the above table. I am using a formula from this blog article:
Filter unique distinct row records in excel 2007

Explaining array formula

If we convert the cell references to named ranges, the array formula becomes easier to read and understand.

=SUM(1/COUNTIFS(ColA, ColA, ColB, ColB, ColC, ColC, ColD, ColD)) + CTRL + SHIFT + ENTER

Step 1 - Count each record in data set

COUNTIFS(ColA, ColA, ColB, ColB, ColC, ColC, ColD, ColD) counts the number of times all criteria match on each row

Example,

The first record is Sample0, B, 11, AA111. The only row where these criteria match is the first one. So the first number in the returning array is 1.

The next record is ... and so on.

Step 2 - Sum records

COUNTIFS(ColA, ColA, ColB, ColB, ColC, ColC, ColD, ColD)

returns

{1, 1, 2, 1, 1, 1, 3, 2, 4, 2, 2, 4, 2, 1, 3, 3, 4, 2, 2, 1, 4, 2, 2, 2}

SUM(1/{1, 1, 2, 1, 1, 1, 3, 2, 4, 2, 2, 4, 2, 1, 3, 3, 4, 2, 2, 1, 4, 2, 2, 2})

becomes

SUM({1, 1, 0,5, 1, 1, 1, 0,333333333333333, 0,5, 0,25, 0,5, 0,5, 0,25, 0,5, 1, 0,333333333333333, 0,333333333333333, 0,25, 0,5, 0,5, 1, 0,25, 0,5, 0,5, 0,5})

and returns 14 in cell A28.

Download excel sample file for this tutorial.

Count unique distinct records.xlsx
(Excel 2007 Workbook *.xlsx)

Count records with possible blank rows in data set

Array formula in cell A28:

=SUM(IF((A2:A25="")*(B2:B25="")*(C2:C25="")*(D2:D25=""), 0, 1/COUNTIFS($A$2:$A$25, $A$2:$A$25, $B$2:$B$25, $B$2:$B$25, $C$2:$C$25, $C$2:$C$25, $D$2:$D$25, $D$2:$D$25)))

How to create an array formula

Count blank rows/records 

Formula in B28:

SUMPRODUCT((A2:A25="")*(B2:B25="")*(C2:C25="")*(D2:D25=""))

Functions used in this formula:

COUNTIFS(criteria_range1,criteria1, criteria_range2, criteria2...)
Counts the number of cells specified by a given set of conditions or criteria

SUM(number1,[number2],)
Adds all the numbers in a range of cells