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

0
votes

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

2
excelexcel-formula
Similar to this Q =COUNTA(UNIQUE(FILTER(B:B,(A:A=E2)*(B:B>=E3)*(B:B<=F3)))) – chris neilsen
@chrisneilsen the FILTER formula is absent from my Excel version 2016. Also, the link you posted does not include a range function which is what makes this problem uniquely difficult – oryan5000
Filter and Unique are available in the current version, Excel365. If you are using an old version, perhaps you should have said so in your Q. Re "does not include a range function" that's why I said "similar" (and didn't close your Q as a dup), and gave you a version of the 365 formula that handles the range. Also the linked Q has an answer for earlier versions, which you should be able to adapt to include the range requirement. – chris neilsen
@chrisneilsen I dont understand how the formula in the linked question is relevant. It does not include a range component to it. I modified the formula to =SUMPRODUCT((A2:A11="Dolphin")/(COUNTIFS(B:B,B2:B11,A:A,"Dolphin")+(A2:A11<>"Dolphin"))). This gives the answer as 4 not 3 because it doesnt exclude the one observation outside of the range. Any suggestions on how to correct this? – oryan5000

2 Answers

1
votes

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

0
votes

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