Formula to count consecutive values reference to another value

[Gaurangg]

Hi Friends,

I am trying to build a formula to count consecutive value in a column, But I failed to get the count.

I would like to get a value of "Yes" counting "Apple" when it comes very next or very before Orange.


[TABLE="width: 159"]
<tbody>[TR]
[TD]Details[/TD]
[TD]Required Result[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Grapes[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Guava[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Grapes[/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

 

[Gerald Higgins]

Why does the first instance of Apple generate a YES ?
It is not immediately before an instance of Orange.

If you need a formula to calculate the YES, perhaps something like this . . . assuming your first instance of Apple is in cell A2, insert this formula into B2, and copy down
=IF(AND(A2="Apple",OR(A3="Orange",A1="Orange")),"YES","")

Do you want to do something different, if Apple appears between two Oranges ?
Or if Orange appears between two Apples ?

 

[Gaurangg]

Hi Gerald,

Thanks for your prompt help. I would like to have written yes in front of Apple when Orange appears between Apples. But the concern is don't know how many apples will come just after or before orange. It may be two, three, four but upto 30. I have tried couple of ways by concatenating and getting count by countif function. also tried your formula and amended it but still not getting the result. should I try by adding multiple condition in "OR(A3="Orange"... formula?

 

[Marcelo Branco]

Maybe...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Details​

[/TD]
[TD]

Required Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Apple​

[/TD]
[TD]

Yes​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Apple​

[/TD]
[TD]

Yes​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Orange​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Apple​

[/TD]
[TD]

Yes​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Grapes​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Apple​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Grapes​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

Apple​

[/TD]
[TD]

Yes​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

Orange​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

Grapes​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in B2 copied down
=IF(A2="Apple",IF(OR(A1="Orange",A3="Orange"),"Yes",IF(A1="Apple",B1,IF(IFERROR(COUNTIFS(A2:INDEX(A2:A$100,MATCH(1,INDEX(--(A2:A$100="Orange"),),0)),"<>Apple",A2:INDEX(A2:A$100,MATCH(1,INDEX(--(A2:A$100="Orange"),),0)),"<>Orange"),1)=0,"Yes",""))),"")

Hope this helps

M.