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

Question

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?

Ike · Accepted Answer · 2022-11-25 16:59:36Z

1

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. .

edited Nov 25, 2022 at 16:59

answered Nov 25, 2022 at 16:54

Ike

13.4k6 gold badges20 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rioZg Over a year ago

Thanks. I will need some time to digest this but I get the idea.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related