Find out the number of unique products in the list (Second formula Explained)

We have covered the first formula last week, what about this second formula
=SUM(IF(FREQUENCY(IF(LEN(A1:A100)>0,MATCH(A1:A100,A1:A100,0),""),IF(LEN(A1:A100)>0,MATCH(A1:A100,A1:A100,0),""))>0,1))?

Before we share with you how the different functions are applied, do take note that the function starts with Sum(if and not sumif. There is a bracket in between the sum and the if. This denotes that it is a multi-conditional sum. To activate the formula, you need to use Ctrl + Shift + Enter together.

What are the conditions in the multi-conditions sum? Let's break them up and see what they meant

Click here for diagram


The formula Len(B1:B8)>0 is used to determine that the cell in the range is not a blank. If there is a character in the cell, then the length is at least 1 and it will return true as shown.

The formula shown in column D is developed to indicate the first row that the content shows up. For example, Internet marketing in row 6 returns a value of 3 because the phrase "internet marketing" shows up the very first time in Row 3.

In Column E, the formula frequency will calculate the number of times the number 1 shows up in the list in column D, followed by 2, 3, ..etc. Notice the simlarity in this formula with the complete formula =SUM(IF(FREQUENCY(IF(LEN(A1:A100)>0,MATCH(A1:A100,A1:A100,0),""),IF(LEN(A1:A100)>0,MATCH(A1:A100,A1:A100,0),""))>0,1)) In fact, the range D1:D8 as shown in the simplified formula is replaced by the formula show in prior column D. The replacement moves us closer to the complete formula. For clarity, the simlarities are highlighted in blue and green colour. Different colours are used to show that the range D1:D8 are replaced twice.

=SUM(IF(FREQUENCY(IF(LEN(A1:A100)>0,MATCH(A1:A100,A1:A100,0),""),IF(LEN(A1:A100)>0,MATCH(A1:A100,A1:A100,0),""))>0,1))

In the final part shown in column F, the formula =if(E5>0,1,0) is used to pass instruction to Excel to put in the number 1 in those rows in column F where the numbers showed up in column E is more than 0. By putting the conditional sum in the formula, we have completed the entire formula where the end result shown is the number of unique records in the list. Do remember to use Ctrl + Shift + Enter together to effect the formula.