Forum Discussion
Count the number of dates that fall within a certain month
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!
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.
14 Replies
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:
- 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
Easiest way is to create PivotTable. If with formula, in column D do you have texts or dates formatted as mmm/yy ?
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)
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
