Filter + choose function challenge |General Excel Questions & Answers|Excel Forum|My Online Training Hub

Hi Mynda, 

I´ve learned great tricks from the filter function video and I actually used your trick for a current task I have. However I´m facing a challenge i haven´t been able to resolved yet. I attacehd a sample file. 

The taks is about finding how often a report was delayed due to a category selected (G2)? I used for the sample just three categories.

As you might see in the file I built a helper column D and E to chek first if the report was delayed and to count the total times the report was delayed. I get with the FILTER plus the CHOOSE formula which reports were delayed and how many times in total (columns I and J) but as you might noticed report 6 was delayed in total 6 times but just 3 of them were due to technical issues, and the same problem with the other reports.

Is there a way to make this dynamic? even though I get the reports delayed due to a certain category I need to get the times they were delayed due to that category. 

I try with a countif and then return the whole array with the offset or the index formula as you had taught but it seems like is not a long term solution, since I don´t get the data properly sorted 🙁 I also tried to wrap the whole function in a COUNTA function but I don´t get of course an array but a number. I have no idea how to get those numbers to be dynamic. 

I would really appreciate if you can take a look and help me glance in front of my Boss once again 🙂 (your whole videos contents had helped me to look like a really excel expert 🙂 thanks for that)

Thank you very much and have a great time and a happy new year!

Regards, 

Maria