Formula to locate second occurrence with multiple conditions met

**ceci773** · 03-17-2009, 08:29 PM

Hi,

I need help with a formula.

The purpose of the formula is to locate the 2nd occurrence of an entry where criteria in other columns are met (used to determine whether people can qualify for an item based on attendance and another condition).

I am using this formula but I know it needs some adjustments and can't seem to work it out or maybe am going about it completely wrong.

=IF(COUNTIF(D2:D205,D2)>1,AND(T2="Y",U2="Y")*1,0)

I need the formula to indicate the second occurrence of this cell with the criteria that BOTH first & second occurrences have T2="Y" and U2="Y". My formula only indicates the first occurrence and the first occurrence if T2 and U2 meet the conditions.

Any suggestions would be highly appreciated. Thanks in advance!

**mubashir aziz** · 03-18-2009, 04:07 AM

try it ..... I've applied in column a, b & c . It will show 2nd and onwawrd equal items .....

=and(countif($a$2:$a$31,$a6)>1,countif($a$2:$a6,$a6)>=2,b6="y",c6="y")

**ceci773** · 03-22-2009, 06:12 PM

Hi, thanks for your reply. The formula is a big improvement, however, it seems to indicate TRUE for both 1st and 2nd occurrences. Can it be adjusted to indicate 2nd occurrence only? Or does it do that for you and not for me?

Thanks again.

**daddylonglegs** · 03-22-2009, 06:49 PM

Hello cec177,

Can you explain again what you want to do, I'm not clear?

Are you looking for a formula for each row? Perhaps an example would help......

**ceci773** · 03-22-2009, 07:03 PM

Hi, I have attached an example.

Basically the formula is needed to identify persons who meet two conditions - being eligible and have attend two courses.

As my working spreadsheet is really big, it would make life so much simpler if I had a formula to locate the 2nd occurrence (and not the first) because only then will the persons have met the requirements.

Thanks and let me know if you need more info.

**daddylonglegs** · 03-22-2009, 07:08 PM

Now I see.....

Try this formula in E2 copied down

=IF(SUMPRODUCT(--(B$2:B2=B2),--(C$2:C2="Y"),--(D$2:D2="y"))=2,"Yes","")

That will give "Yes" against the 2nd occurence only (not the first or the third or any subsequent occurence). If you want "Yes" shown against every occurence after the 2nd then change =2 to >=2

**ceci773** · 03-22-2009, 08:32 PM

Great, I understand now. Thanks again

Formula to locate second occurrence with multiple conditions met

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Formula to locate second occurrence with multiple conditions met

Re: Formula to locate second occurrence with multiple conditions met

Re: Formula to locate second occurrence with multiple conditions met

Re: Formula to locate second occurrence with multiple conditions met

Re: Formula to locate second occurrence with multiple conditions met

Re: Formula to locate second occurrence with multiple conditions met

Re: Formula to locate second occurrence with multiple conditions met

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions