countifs in Excel 365

[stephen1905]

Hi Hive

I'm struggling with the below "countifs" statements.  this is a simplified version of my actual formula, but I'm getting the same weird output.

Basically, I'm trying to count up the number of dates in column B that are greater than or equal to dates in column A by month(defined in cells D1 & E1), and where column B is not blank.

This is my formula in cell D2:

=COUNTIFS($A$2:$A$5,">="&D1,$A$2:$A$5,"<="&EOMONTH(D1,0),$B$2:$B$5,"<>"&"",$B$2:$B$5,"<="&@$A$2:$A$5)

and in E2:

=COUNTIFS($A$2:$A$5,">="&E1,$A$2:$A$5,"<="&EOMONTH(E1,0),$B$2:$B$5,"<>"&"",$B$2:$B$5,"<="&@$A$2:$A$5)

Planned	Actual		Sep-21	Oct-21
01/09/2021	31/08/2021		2	0
02/09/2021	01/09/2021
03/09/2021	02/09/2021
01/10/2021	30/09/2021

using the current values in column B, my actual answers should be 3 for September and 1 for October.  I think this may be with the new spill feature in excel 365 as I don't think I've had this problem before.  The "@" was added by excel 

my logic in the formula is:

count where cells in range $A$2:$A$5 are greater than or equal to start of month

AND

where cells in range $A$2:$A$5 are less than or equal to end of month

AND 

where cells in range $B$2:$B$5 are not equal to "" 

AND

where cells in range $B$2:$B$5 are less than or equal to their corresponding cells in range $A$2:$A$5 (eg B3 <=A3)

To get round this problem, I've added a helper column with formulas to my workbook, but I'd rather not use that if I can solve this problem.

Any suggestions much appreciated

[#2]

@stephen1905 

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.

[#3]

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?
Thanks

[#4 · Accepted solution]

@stephen1905 

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

[#5]

Thanks Mathetes

exactly what i wanted.
many thanks