Forum Discussion
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
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
- SergeiBaklanDiamond Contributor
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.
- stephen1905Copper ContributorThanks 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- mathetesGold Contributor
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