Count Date Based on Month

[cyliyu]

What will be the formula to count dates (column) based on the same month and names them in order?
e.g. 4 dates in Feb and 2 dates in Mar, the new column ID will be 1,2,3,4 for Feb and 1,2 for Mar. Thanks.

and the ID will reset when the year changed.

[wk9128]

@cyliyu welcome to the forum

Please read and refer to this link, how to upload attachments,follow mathod 2
https://www.excelforum.com/the-water...his-forum.html

Please add some manually calculated expected results
Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information
(10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click,
and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen

[cyliyu]

Please see the attached file the sample.

[MarvinP]

Hi cyliyu,

You can sort your column D using Custom Sort using the built in Jan, Feb, Mar... sort order. If you do that then column A could be =Row()-1 and pull it down.

https://support.microsoft.com/en-us/...4-a02205834d72
See this link for Custom Sort by date.

OR...
In A2 put this formula and pull it down

Please Login or Register  to view this content.

Reformat Column A to number...

[cyliyu]

Thank you very much, MarvinP.

The formula will renumber the rows in sequential form.
Refer to the sample file, using the formula and expected outcomes.

I created the column because when I used Pivot Table and filtered say, April as the Month, it displayed 5,6,7 (based on column A) instead of 1, 2, 3.

[wk9128]

ANS. POST#1
Cell A2 formula

Please Login or Register  to view this content.

ANS. POST#5
Cell I2 formula

Please Login or Register  to view this content.

[cyliyu]

Thanks, wk9128.

The formula works great.

[wk9128]

@cyliyu You're Welcome. Glad to help . Thank You for the feedback and rep.

If you finally get a solution please mark your thread as SOLVED:
- Click Thread Tools above your first post,you will see the word PREFIX on the upper left, press the button to select [SOLVED] select "Mark your thread as Solved".

[cyliyu]

I've noticed that as more rows are added to the Table, the formula shows an error.
The previous row with the same month will have the same number as the new row.
Attachment 774181

[Bo_Ry]

Please try

=COUNTIFS(D$2:[@Since],">"&EOMONTH(D2,-1),D$2:[@Since],"<="&EOMONTH(D2,0))

[cyliyu]

Thank you very much, Bo Ry.
Your formula is really excellent.
I suspect the sumproduct formula may not function effectively in Table.

[Bo_Ry]

SUMPRODUCT also works but the array formula calculates slower than COUNTIFS

=SUMPRODUCT(--(TEXT(D$2:[@Since],"my")=TEXT(D2,"my")))

=SUMPRODUCT(--(EOMONTH(+D$2:[@Since],0)=EOMONTH(D2,0)))

[cyliyu]

SUMPRODUCT also works but the array formula calculates slower than COUNTIFS

=SUMPRODUCT(--(TEXT(D$2:[@Since],"my")=TEXT(D2,"my")))

=SUMPRODUCT(--(EOMONTH(+D$2:[@Since],0)=EOMONTH(D2,0)))

Noted with Thanks. Bo_Ry

[MarvinP]

Hi cyliyu,

The newer attachment with the "Expected Result" would have gotten you a better answer much sooner in this thread.
Sorry I missed the expectations in your question.