If condition

[yaelcohen]

Hello

I am asking for help

What should be the condition if I need all these firms that have in there data the years 2008,2009,2010 (indicate in "1"). If one of these years is missing - this firm should not indicate in 1.

Attached an example

I appreciate for any clue

Yael

 

[NARAYANK991]

Hi ,

See if this works.

Narayan

 

[yaelcohen]

Yes, it works
thanks a lot!

 

[Biplab4444]

Hi...
Check this one with helper column

 

[NARAYANK991]

Hi...
Check this one with helper column

Hi ,

Check your solution by entering the same year in 3 cells , say C13 , C14 and C15.

Narayan

 

[yaelcohen]

Thanks a lot

 

[MohanBabu1023]

Hi Narayan,

Formula seems Superb

can u able to explain to understand the formula.

Regards,
Mohan

 

[Biplab4444]

Hi ,

Check your solution by entering the same year in 3 cells , say C13 , C14 and C15.

Narayan

Hi Narayan,

Thanks for the valuable input....

Now check this formula without helper column....
=IF(AND(COUNTIFS($A$2:$A$15,A2,$B$2:$B$15,2008)=1,COUNTIFS($A$2:$A$15,A2,$B$2:$B$15,2009)=1,COUNTIFS($A$2:$A$15,A2,$B$2:$B$15,2010)=1,OR(B2={2008,2009,2010})),1,"")
Please refer sheet 2........

 

[bosco_yip]

=IF(AND(COUNTIFS($A$2:$A$15,A2,$B$2:$B$15,2008)=1,COUNTIFS($A$2:$A$15,A2,$B$2:$B$15,2009)=1,COUNTIFS($A$2:$A$15,A2,$B$2:$B$15,2010)=1,OR(B2={2008,2009,2010})),1,"")

The above formula can be shortened to this non-CSE formula:

=IF((SUM(COUNTIFS(A$2:A$15,A2,B$2:B$15,{2008,2009,2010}))=3)*OR(B2={2008,2009,2010}),1,"")

Regards
Bosco