Forum Discussion

stephen1905's avatar
stephen1905
Copper Contributor
Sep 20, 2021
Solved

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)

PlannedActual Sep-21Oct-21
01/09/202131/08/2021 20
02/09/202101/09/2021   
03/09/202102/09/2021   
01/10/202130/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

 

 

  • mathetes's avatar
    mathetes
    Sep 20, 2021

    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

     

     

4 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor

    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.

    • stephen1905's avatar
      stephen1905
      Copper Contributor
      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
      • mathetes's avatar
        mathetes
        Gold Contributor

        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

         

         

Resources