Count the number of dates that fall within a certain month

[Bourrinho]

Hi,

I'm looking for a formula that counts the number of dates in a list fall within a certain month. If at all possible without intermediate steps. 

To be more specific, I'm wondering if there's a formula that can perform the count as in the yellow cells below (now I added these manually). So counting in a list of dates (column B below) how many in month/year A, month/year B, etc

Thanks a lot for the help!

[#2]

@Bourrinho 

Easiest way is to create PivotTable. If with formula, in column D do you have texts or dates formatted as mmm/yy ?

[#3]

@SergeiBaklan 

In column D it's dates formated as mmm/yy. 

Would a pivot also work if there's multiple columns to count? I used a simplified example but in reality will be about 10 columns of dates (e.g. project 1 registration, Proj 2,...). Then I would like to have the count per month per project (ie a table with for example projects listed horizontally and then listing the applicable months in date format as rows below)

[#4]

@Bourrinho 

Nope, PivotTable won't work such way, at least directly. As variant that could be like

=SUMPRODUCT( --(MONTH(range)=MONTH(fromDateCell))

assuming in range are dates

[#5]

@Bourrinho 

You can make short work of this task by unpivoting the registration columns and then creating a pivot table.

Unpivot columns - Power Query | Microsoft Learn

The unpivoting will arrange your data vertically so it's easy to pivot:

[#6]

Problem here could be that months can have a different year. (could e.g. be Feb 2023 and 2024 in list)

[#7]

Issue here is that there's also other info listed on the sheet. However I think I maybe can get it work by creating a new sheet and transfer info required for A-C

[#8]

@Bourrinho 

That could be

[#9]

This formula works. Thanks!

Just an advanced question: is it also possible to look only look for the dates in odd rows with the same formula?

[#10 · Accepted solution]

@Bourrinho 

In general that could be

=SUMPRODUCT(
    ( IFERROR( N(MONTH($B$2:$D$7)), 0) = MONTH($F2) ) *
    ( IFERROR( N(YEAR($B$2:$D$7)), 0) = YEAR($F2) )*
    ISODD( ROW($B$2:$D$7) )
)

by adding one more criteria. Details depends on content, do you mean odd row within the grid, or odd row counting from the start of the range.

[#11]

@SergeiBaklan 

I mean odd row within the grid. Like in example below, in the columns C and E the even rows show the window for the activity, while the odd rows represent the actual date. I would like to count the months like in the table on the right. For the registrations, your earlier formula worked. For the completions, I would need to exclude the even rows since the windows of completion don't have to be counted.

My apologies for these additional questions. But while "playing around" new ideas come up 

[#12]

@Bourrinho 

When you need to do that column by column. Registration dates are in even rows, competition ones - in odd rows.

Applied to columns separately mentioned formula shall work.

[#13]

Thanks, could make it all work!

[#14]

@Bourrinho 

Great to know, thank you for the feedback

[#15]

@SergeiBaklan 

Wondering if it's possible to add an additional criterion. Like in below example calculate how many registrations in a certain month but only for new students (so only for those indicated with "Yes" in column C). Or in other words, an automated count in the cells in yellow.