Excel Count from IF Array-CodePudding

I have two tables containing data on names with a pass/fail variable. I am trying to count how many people originally failed but then passed later on.

Name	Passed
Mike	Passed
John	Failed
Billy	Failed

Name	Passed
Mike	Passed
John	Passed
Billy	Failed

I originally did this by creating a third table with an IF array that put out the names of the people who failed. I then counted the amount of "TRUE"s.

For Name:

=IFERROR(IFS(Table13[Passed]="FAILED", Table13[Name]), "")

For Passed Later:

=IF(XLOOKUP(VALUETOTEXT(A2),Table1[Name],Table1[Passed])="Passed",TRUE,FALSE)

Name	Passed Originally	Passed Later
#N/A	#N/A	#N/A
John	Failed	TRUE
Billy	Failed	FALSE

And finally got the count by

=COUNTIF(Table3[Passed Later], "=TRUE")

I was able to skip the name column and got the Passed Later column directly by combining the first two formulas into

=IF(XLOOKUP(IFS(Table13[Passed]="Failed", Table13[Name]),Table1[Name],Table1[Passed])="Passed",TRUE,FALSE)

Now I am stuck at combining the COUNTIF Function. I do not know how I can implement this all into one function, if that is possible. Any advise? I think my main problem is the output of the Passed Later column is not numbers, but strings.

CodePudding user response：

Not sure if this is the correct answer, but it works.

After typing:

I think my main problem is the output of the Passed Later column is not numbers, but strings.

I was able to figure it out. I just needed to give a number output on the last if statement for "Passed" and a string for "Failed". Here is my final formula, at least the solution I came to.

=COUNT(IF(XLOOKUP(IFS(Table13[Passed]<>1, Table13[Name]),Table1[Name],Table1[Passed])=1,1,""))

Figured I would leave this up to help others.

CodePudding user response：

Use Two Filters and check if the first is found in the second with IsNumber(Match())

=LET(
    fst,FILTER(Table13[Name],Table13[Passed]="Failed"),
    scd,FILTER(Table1[Name],Table1[Passed]= "Passed"), 
    SUM(--ISNUMBER(MATCH(fst,scd,0))))

If you want the name of who failed then passed add another Filter:

=LET(
    fst,FILTER(Table13[Name],Table13[Passed]="Failed"),
    scd,FILTER(Table1[Name],Table1[Passed]= "Passed"),
    FILTER(fst,ISNUMBER(MATCH(fst,scd,0))))

CodePudding user response：

Would the following work:

Formula in G1:

=FILTER(A2:A5,COUNTIFS(A2:A5,A2:A5,B2:B5,"Failed")*COUNTIFS(D2:D5,A2:A5,E2:E5,"Passed"))

Or, if you want a count:

Formula in H1:

=SUM(COUNTIFS(A2:A5,FILTER(D2:D5,E2:E5="Passed"),B2:B5,"Failed"))