Formula to locate second occurrence with multiple conditions met

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!

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