Formula to Count unique items based on multiple criteria including time period

Hi,

I would like to count the number of unique values/concepts (data on the left-hand side) based on the following criteria:
1. Category Code - it will only count the number of unique concepts for that category
2. Time Period - if I select from 1 to 3, then it should sum the number of unique concepts based on time periods 1-3. If I select from 3 to 3, then it would only count the number of unique concepts based on time period 3.

Sample file attached for reference.

Originally Posted by Ali Kırksekiz

For I6 cell

=SUM(IF(FREQUENCY(IF($C$2:$C$100<>"",IF($I$2<=$A$2:$A$100,IF($A$2:$A$100<=$J$2,IF($B$2:$B$100=$H6,MATCH("~"&$A$2:$A$100,$A$2:$A$100&"",0))))),ROW($A$2:$A$100)-ROW($A$2)+1),1))

This is an Array Formula confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula then drag down up to I12

No. of Unique Concept Names
Fragrance 2
Color 1
Skin Care 3
Personal Care 2
Hair Care 1
Innerwear 3
Jewelry 3

Total 15

Tried using your formula but looks like it's retrieving incorrect information.. For example, fragrance should return 5 unique concept names (SCENT ESSENCE VIBRANT FRUITY, SCENT ESSENCE ROMANTIC BOUQUET, SCENT ESSENCE SPARKLY CITRUS, X-SERIES QUAKE AP, Sweet Miss BRILLIANCE EDT) from time periods 1 to 3..

I changed the formula. Try again please.

Originally Posted by Ali Kırksekiz

I changed the formula. Try again please.

Thanks! What if create a new row (column D) with values in it and I add another criteria wherein the value should be greater than zero for the unique concepts to be counted. What will be the revised formula? I have attached a new file for reference.

In summary, here's the new criteria:
1. Category Code - it will only count the number of unique concepts for that category
2. Time Period - if I select from 1 to 3, then it should sum the number of unique concepts based on time periods 1-3. If I select from 3 to 3, then it would only count the number of unique concepts based on time period 3.
3. Units value - should be greater than zero for the unique concept to be counted.