3

I have some data in excel, I want to select unique values based on multiple criteria.

The data that I have in excel is

enter image description here

Name Medals Year

  • A 2 2017
  • B 3 2018
  • C 5 2018
  • A 1 2016
  • C 4 2017
  • B 7 2018
  • A 1 2017
  • D 4 2016

I want to get the count of unique names who got medals >2 and <6 and year is 2017 or 2018.

So the result that I should get is 2 as B and C satisfy the criteria.

I have searched in internet about his and I got some formula using SUMPRODUCT and COUNTIFS, the formula used is

=SUMPRODUCT(1/COUNTIFS(A2:A9,A2:A9,B2:B9,">2",B2:B9,"<6",C2:C9,">2016",C2:C9,"<2019" ))

But I am getting error in this case.

Please suggest me how can I get the desired count. Thanks in advance!!

Note: This data is excerpted from huge data.

Sivakesav
  • 31
  • 8

2 Answers2

3

So if this is your data:

enter image description here

The formula used in E9:

{=SUM(--(FREQUENCY(IF((B2:B9>2)*(B2:B9<6)*(C2:C9>2016)*(C2:C9<2019),MATCH(A2:A9,A2:A9,0)),ROW(A2:A9)-ROW(A2)+1)>0))}

See here for more in depth explaination.

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • 1
    Impressive array formula. Yeah I was wondering, because I had a formula of `COUNTIFS()` prepared, but could not figure a way to pass it to a `FREQUENCY` – Samuel Hulla May 09 '19 at 12:47
  • @Sivakesav, You need to make your references absolute! Except the last `ROW(A2)` – JvdV May 10 '19 at 09:08
  • Thank you so much @Jvdv , your explanation helped me. – Sivakesav May 10 '19 at 09:56
  • @Sivakesav, I'm glad to help. Please consider to mark your question as answered. See [here](https://stackoverflow.com/help/someone-answers) for help. – JvdV May 10 '19 at 09:58
0

Another option would be to use a with

  • Year filtered as as >=2017

    enter image description here

  • Name Values between 2 and 6

    enter image description here

  • Which yields the following result:

    enter image description here

  • and then we simply count unique values (excluding blank cells <>"")

enter image description here

Would say this formula is a bit more user-friendly and makes for a better visual representation of the data, but if you just want a formula, then absolutely go for @JvdV's solution :)

Samuel Hulla
  • 6,617
  • 7
  • 36
  • 70