Count unique values with condition
LaPimpa
Posted messages
7
Status
Membre
-
LaPimpa Posted messages 7 Status Membre -
LaPimpa Posted messages 7 Status Membre -
Hello community,
The question has been raised several times, but none of the solutions found work in my file, and your help would be greatly appreciated!
I am looking for a formula without VBA to count the number of unique companies by industry.
Example:
In column A: company names (with duplicates)
In column B: the company’s industry
- the company KIKI appears 4 times (column A), in the category "Food Industry" (column B)
- the company BABA appears 2 times (column A), in the category "Food Industry" (column B)
I therefore have 2 unique companies in the "Food Industry" category.
To calculate this, I tried the following formula:
=SUMPRODUCT(1/COUNTIF($A1:$A1000;$A1:$1000)*(B1:B1000="Food Industry"))
This formula gives me a result with decimals (strange) and which is not accurate.
Do you have any idea what the problem is?
Thank you!
Pimpa
Configuration: Windows / Firefox 72.0
The question has been raised several times, but none of the solutions found work in my file, and your help would be greatly appreciated!
I am looking for a formula without VBA to count the number of unique companies by industry.
Example:
In column A: company names (with duplicates)
In column B: the company’s industry
- the company KIKI appears 4 times (column A), in the category "Food Industry" (column B)
- the company BABA appears 2 times (column A), in the category "Food Industry" (column B)
I therefore have 2 unique companies in the "Food Industry" category.
To calculate this, I tried the following formula:
=SUMPRODUCT(1/COUNTIF($A1:$A1000;$A1:$1000)*(B1:B1000="Food Industry"))
This formula gives me a result with decimals (strange) and which is not accurate.
Do you have any idea what the problem is?
Thank you!
Pimpa
Configuration: Windows / Firefox 72.0
Best regards,