0

I have a file with data in the following format:

text value1 value2

Given value 1 and value 2 meet some criteria, find all the unique text values.

The exact data looks like this:

john 10 20
john 15 35
mark 20 10
mark 25 15
tom  25 40
lee  16 50

If val 1 <=25 and value 2 <=35 the number of unique text = 2 (john and mark) I have to do this using formulas not filters.

I've been trying combinations of frequency, countifs, sumproducts and a whole range of other methods and can't seem to hit what I'm looking for.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Marston Gould
  • 36
  • 4

3 Answers3

0

The following formula will do what you are asking:

=SUM(IF(FREQUENCY(IF(B2:B7<=25,IF(C2:C7<=35,MATCH(A2:A7,A2:A7,0),""),""),IF(B2:B7<=25,IF(C2:C7<=35,MATCH(A2:A7,A2:A7,0),""),""))>0,1))

This is an array formula so confirm it with Ctrl-Shift-Enter.

I referred to this webpage.

Also found a shorter one:

=SUM(--(FREQUENCY(IF(B2:B7<=25,IF(C2:C7<=35,COUNTIF(A2:A7,"<"&A2:A7),""),""),COUNTIF(A2:A7,"<"&A2:A7))>0))

Found and modified from hre.

Community
  • 1
  • 1
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
0

If you want to list the unique values rather than count them, something like this:-

=IFERROR(INDEX(A$2:A$7,MATCH(0,IF((B$2:B$7>25)+(C$2:C$7>35),1,COUNTIF(E$1:E2,A$2:A$7)),0)),"")

entered as an array formula starting in E2 ( and assuming that you are using columns A,B and C for your data.

See this reference for explanation.

enter image description here

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
0

Assuming that text, value1, and value2 are in columns A, B, and C respectively ...

In D1, enter the formula =IF(AND(B1<=25,C1<=35),A1,"") and copy it down the column

Use the formula =SUMPRODUCT((D:D<>"")/COUNTIF(D:D,D:D&"")) for your answer

Kennah
  • 487
  • 1
  • 5
  • 16