Count if entry for specific account is Failed

Try this formula for W:

=IF(AND($A2="W",COUNTIF(OFFSET(I$1,0,0,ROW()-1),1)>0),"1",IF($A2="W",MIN(1,COUNTIF($B2:$H2,"FAILED")),""))

Put it into Cell I2 and copy it down. 

This will output 1 if there is a FAILED value in the row and the type is W and it will also check if there is any other 1 above it in the column and output 1 if that is the case. I think this is what you were trying to do.

To change for the other types, just copy the formula to the right and change W to the correct account type.

Here is a formula you may like to use.

[I2] =IFERROR(IF(INDEX($A2:$H2,1,MATCH(I$1,$A2:$G2,0)+1)<>"PASSED",1,0),0)

Copy it from I2 to range I2:L2. In order to make the reference to I$1 work, the cell format in I1:L1 must be changed. Set a Custom cell format "FAILED WITH "@ and type W, X, Y and Z in the cells. The apperance will be the same but the cell values will only be W, X, Y and Z. As an alternative you might replace the reference to I$1 with a hard "W" and proceed analogue in the other columns so that you have a different formula in each column.

Note that the formula presumes that each account will appear only once in each row. It will count any Status which isn't PASSED as failed.

The formula will display a zero both if the account isn't found in a row and if its Status is PASSED. If you don't like to see this zero follow these steps:-

1. Click File / Options / Advanced
2. Look for Display options for this worksheet.
3. Look for Show a zero in cells that have zero value and remove the checkmark.

Edit 13 Oct 2018  ================================

The formula below has been modified to write "1" in the column when the status is eitehr "Failed" or "No Show".

=IFERROR(IF(OR(INDEX($A2:$H2,1,MATCH(I$1,$A2:$G2,0)+1)="FAILED",INDEX($A2:$H2,1,MATCH(I$1,$A2:$G2,0)+1)="NO SHOW"),1,0),0)

As a matter of principle, it is best practice not to write variables into formulas. There are two variables in the above, "FAILED" and "NO SHOW". It would be better to put them in a list, possibly on another worksheet, and refer to the list in the formula.

The same goes for the reference to I$1. If you can't modify the column headers I would prefer to have the card names in a list, possibly on another sheet, and refer to that list. This is just to avoid fidgeting with the formula when the variable is changed. However, you can also replace I$1 with a string, like "MasterCard" or "Visa". The difference is whether you replace it in one location or in every formula.

At the very end of the formula you have two zeroes "NO SHOW"),1,0),0). The first one will be used if the text is neither "Failed" nor "No Show". The second and last one will be used if the card isn't found (like row 2 has no "Y"). You may wish to replace one or both of these zeroes with "" (twin quotation marks = null string)

In the attached workbook, on Sheet1(2), a formula is used that implements the suggestions made above. The tables used by this formula are on Sheet3.