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

Please Login or Register  to view this content.

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.