Formula to locate second occurrence with multiple conditions met

**ceci773** · 03-17-2009, 09: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, 05: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, 07: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, 07: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, 08: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, 08: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:18 PM

Thank you!!! That works beautifully!

I've never quite worked out how to use sumproduct. Would you mind breaking it down for me so that I may be able to manipulate it in future?

Thanks again!

**daddylonglegs** · 03-22-2009, 09:17 PM

In its simplest form it's just used to get a sum of products, as the name implies, e.g. this formula

=SUMPRODUCT(A1:A3,B1:B3)

where A1:B3 contains numbers will give a result equal to =(A1*B1)+(A2*B2)+(A3*B3)

often, though, it's used for multiconditional counting and/or summing, e.g.

=SUMPRODUCT(--(A1:A3="y"),--(B1:B3="x"))

will count the number of rows that have "y" in column A and an "x" in column B....this works because the A1:A3="y" part returns an array of TRUE/FALSE values and -- converts these to 1/0 so you get a sum of products as above....but all rows will be zero, unless both conditions are TRUE, in which case you get 1 for that row, so the result is effectively a count of rows where both conditions are met.

For your specific case you are using 3 conditions, B must match the name, C and D must both equal "Y", and a twist for this case is that you only look at rows equal to or above the current row, hence the single $ sign in B$2:B2=B2 etc.... so when the count of rows satisfying the conditions is 2, you get "Yes".

Thinking about it a little more I realise that the formula would also give "Yes" at a subsequent row where the count was 2, but not actually achieved at that row, so you probably need to include an extra condition, i.e.

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

For more on SUMPRODUCT see here