CODEWITHSUNDEEP
excelexcel-formula
oryan5000
oryan5000

Reputation: 91

Excel: Formula to count number of unique values in range that match a condition

Consider the hypothetical scenario that you are given a dataset with animals in column A and their weights in column B. Suppose that we want to count the total number of unique weight values within a certain range for a specific animal. In the example below, I'm wanting to count the total number of unique Dolphin weights between 100 and 200. There are three unique weights: 126, 155 and 171. The duplicate weight of 126 is only counted once. The weight of 90 is ignored because it is not within the range. What custom formula can be used to solve this problem?

*This is for Excel 2016

enter image description here

Upvotes: 0

Views: 614

Answers (2)

Harun24hr
Harun24hr

Reputation: 37100

If you are interested to go with SUMPRODUCT() then you can give a try to-

=SUMPRODUCT(((A2:A11=E2)*(B2:B11>=E3)*(B2:B11<=F3))/(COUNTIFS(A2:A11,A2:A11,B2:B11,B2:B11)))

enter image description here

Upvotes: 1

chris neilsen
chris neilsen

Reputation: 53136

Adapting Scott's second formula:

=SUM(--(FREQUENCY(IF((A2:A11=E2)*(B2:B11>=E3)*(B2:B11<=F3),B2:B11),B2:B11)>0))

where E2 = "Dolphin", E3 = 100, F3 = 200

Upvotes: 0

Related Questions