Countifs: how can I refer to an entire month rather then a single day?

[Nelson78]

Hello everybody?

Suppose in L10 the following formula:

=COUNTIFS('Sheet1'!$H:$H;Report!$B29;'Sheet1'!$AF:$AF;L1;'Sheet1'!$M:$M;Report!$B$23)

L1 is a date: 27/03/2019

In this case, the result in L10 is a number that considers in Sheet1 column AF only the records dated 27/03/2019.


Now, I'd like to consider all the records dated March 2019 (from 1st to 31th): what do I have to write in L1?

 

[FracinDean]

Can you make another column in Sheet1 that has the year and month concatenated YYYY-MM and use a formula to put the same format in L1?

If the date you want is in some other cell, such as K1, then put this in L1:

=year(K1)&"-"&text(month(K1),"00")

 

[RoryA]

You can use start and end date cells (say L1 and L2) so that you use two criteria:

=COUNTIFS('Sheet1'!$H:$H;Report!$B29;'Sheet1'!$AF:$AF;">="&L1;'Sheet1'!$AF:$AF;"<="&L2;'Sheet1'!$M:$M;Report!$B$23)

 

[Nelson78]

You can use start and end date cells (say L1 and L2) so that you use two criteria:

=COUNTIFS('Sheet1'!$H:$H;Report!$B29;'Sheet1'!$AF:$AF;">="&L1;'Sheet1'!$AF:$AF;"<="&L2;'Sheet1'!$M:$M;Report!$B$23)

This could be a good idea, but I'm not sure when I could have available the end date cell to refer to.

I mean: suppose today is the 15th of March 2019 and I have to calculate the records in the month of March 2019 so far, it is not granted that I already have somewhere in the sheet the end date (1st of April 2019). Presumibly, I will have it avalaible from the 2nd of April 2019 in cell M1.

 

[RoryA]

If L1 always has a date in the month you're interested in, you could use:

=COUNTIFS('Sheet1'!$H:$H;Report!$B29;'Sheet1'!$AF:$AF;">"&EOMONTH(L1;-1);'Sheet1'!$AF:$AF;"<="&EOMONTH(L1;0);'Sheet1'!$M:$M;Report!$B$23)

 

[Nelson78]

If L1 always has a date in the month you're interested in, you could use:

=COUNTIFS('Sheet1'!$H:$H;Report!$B29;'Sheet1'!$AF:$AF;">"&EOMONTH(L1;-1);'Sheet1'!$AF:$AF;"<="&EOMONTH(L1;0);'Sheet1'!$M:$M;Report!$B$23)

Yes!
Thank's.