Forum Discussion
countifs in Excel 365
If you have the most recent version of Excel, then the dynamic array function FILTER can be used, in conjunction with COUNT (as this image shows)... [You can use multiple criteria in FILTER as well]
Here's a video that Microsoft released to explain FILTER and a couple other Dynamic Array functions. Well worth learning about.
https://www.youtube.com/watch?v=9I9DtFOVPIg
In formula you use &@$A$2:$A$5, that means you compare with value in current row of A2:A5, i.e. all less than or equal to 01 Sep for the first row. It correctly returns 2.
- Thanks for the quick response Sergei.
I don't understand though. 😞
What I was expecting to see with those values is a match for all rows, and therefore D2 should be a total of 3 and E2 a total of 1. I want the total count of rows matching the criteria.
Is there a better formula to use to achieve this?
ThanksIf you have the most recent version of Excel, then the dynamic array function FILTER can be used, in conjunction with COUNT (as this image shows)... [You can use multiple criteria in FILTER as well]
Here's a video that Microsoft released to explain FILTER and a couple other Dynamic Array functions. Well worth learning about.
https://www.youtube.com/watch?v=9I9DtFOVPIg
- Thanks Mathetes
exactly what i wanted.
many thanks
