How can I use COUNTIF and FILTER functions to get a count for the output array?

Question

I have used this function to get how many times a specific input occurred in 3 shifts "M, E and N"

=COUNTA(FILTER(FILTER(Input!$D$3:$E$95,(Input!$D$3:$D$95=Nurses!A2)*(Input!$E$3:$E$95="E")),{0,1}))

So basically, I got the result as 4 when using COUNTA however if the FILTER gives and ERROR message, It turns that COUNTA considers it as a value of 1 which is absolutely not the fact.

I tried to replace COUNTA with COUNTIF

=COUNTIF(FILTER(FILTER(Input!$D$3:$E$95,(Input!$D$3:$D$95=Nurses!A2)*(Input!$E$3:$E$95="E")),{0,1}),"E")

the whole function doesn't run and I keep getting an Error message.

I decided to used IFERROR and IF

=IF(IFERROR(FILTER(FILTER(Input!$D$3:$E$95,(Input!$D$3:$D$95=Nurses!A2)*(Input!$E$3:$E$95="E")),{0,1}),0)=0,0,COUNTA(FILTER(FILTER(Input!$D$3:$E$95,(Input!$D$3:$D$95=Nurses!A2)*(Input!$E$3:$E$95="E")),{0,1})))

The output was an array of 4 times the number of E appearance in the FILTER function.

I hope that someone can help with this simple issue, I am so thankful in adavnce.

I tried Filter, IFERROR, IF, COUNT, COUNTA, COUNTIF

please see How to create a Minimal, Complete, and Verifiable example. Also check out the tour, and help center — Foxfire And Burns And Burns
– Foxfire And Burns And Burns, Commented May 24, 2024 at 12:25
May be you can use something like this : =SUM(1-ISERROR(N(FILTER(Input!$E$3:$E$95,(Input!$D$3:$D$95=Nurses!A2)*(Input!$E$3:$E$95="E"))<>""))) — Mayukh Bhattacharya
– Mayukh Bhattacharya, Commented May 24, 2024 at 12:39
> the whole function doesn't run and I keep getting an Error message. COUNTIF does not take arrays as the first parameter; use of FILTER results in array and thus the error message. — nkalvi
– nkalvi, Commented May 25, 2024 at 3:03
Is it a one-off task or ongoing one? If it is the latter, I would suggest naming ranges and pulling data together and use Pivot Tables. exceljet.net/articles/named-ranges — nkalvi
– nkalvi, Commented May 25, 2024 at 3:51
Appreciated! I am not considering myself and expert in Excel but It's just a one time fixed task. I will try to go with naming as yours works perfectly as the final one I got specially to not include the empty cells. — David
– David, Commented May 26, 2024 at 7:24

Rory · Accepted Answer · 2024-05-24 13:34:01Z

1

You could use:

=IFERROR(ROWS(FILTER(Input!$E$3:$E$95,(Input!$D$3:$D$95=Nurses!A2)*(Input!$E$3:$E$95="E"))),0)

answered May 24, 2024 at 13:34

Rory

34.2k5 gold badges40 silver badges42 bronze badges

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

Collectives™ on Stack Overflow

How can I use COUNTIF and FILTER functions to get a count for the output array?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related