How to add a calculated column to the results of a FILTER function in Excel?-CodePudding

I think Excels FILTER function is great, especially when combined with SORT, CHOOSECOLS and so on.

What I did not even manage to google, let alone find a solution for is: can I "paste" a calculated columns to the results of FILTER function.

For example:

I have the original data on the left, I want to FILTER the rows where a person had passed a test and then for example COUNT how many times have they passed a test.

It is just an example, it can be how many exams have they took in the first trimester, analogously it could be the total amount billed if you look at customer invoices and so on.

The column with the yellow title is the column that I want "pasted", i.e. calculated.

I could do FILTER and then add formulas in the last column as I did here. But it does not adapt to number of rows being filtered, as below (if Carol passed one test less for example).

How to do it or is it for some reason better to do it fundamentally in another way?

CodePudding user response：

If you have Excel 365 current channel you can use this formula:

=LET(d,A24:D35,
f,FILTER(d,INDEX(d,,4)=TRUE),
sPassed,CHOOSECOLS(f,1),
cnt,MAP(sPassed,LAMBDA(s,ROWS(FILTER(sPassed,sPassed=s)))),
HSTACK(f,cnt))

As COUNTIF doesn't work within MAP, you have to use the FILTER function together with ROWS to count how often a student has passed. .