 # countifs in Excel 365

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

4 Replies

# Re: countifs in Excel 365

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.

# Re: countifs in Excel 365

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

# Re: 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.