IF Formula

[kparadise]

Hello,

I have a set of data with a primary ID and a calculated field (number). The primary ID can appear multiple times within the data set, with more than one calculated field values. I am looking for a formula to ask the data set, look through at each primary ID, and determine if at least one of those primary IDs has a calculated value of less than 2.

See Results in Column C. I have down this before with an array formula looking for specific text, but never to ask if any of the values in column B are less than a certain value.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]abc
[/TD]
[TD]-3
[/TD]
[TD]Y
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]abc
[/TD]
[TD]-5
[/TD]
[TD]Y
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]abc
[/TD]
[TD]7
[/TD]
[TD]Y
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]abc
[/TD]
[TD]8
[/TD]
[TD]Y
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]zyx
[/TD]
[TD]2
[/TD]
[TD]N
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]zyx
[/TD]
[TD]3
[/TD]
[TD]N
[/TD]
[/TR]
</tbody>[/TABLE]

 

[dreid1011]

Perhaps this:

=IF(COUNTIFS($A$1:$A$6,A1,$B$1:$B$6,"<"&2),"Y","N")

 

[Marcelo Branco]

Try

C1 copied down
=IF(COUNTIFS(A:A,A1,B:B,"<2"),"Y","N")

M.

 

[kparadise]

Both work perfectly; thank you.