I want to count items within current month without needing to change the month manually

I want to count items within current month without needing to change the month manually

I need to report on the number of items that appear in the current month to date.

Is there a formula that can be applied without necessitating manual entry of changing the month manually?

Best Answer

Answers

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi Terry,

    Yes, there is.

    Where do you want to show the information? In the same sheet, Sheet Summary, Dashboard or somewhere else?

    I hope that helps!

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    ✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    I will use a COUNTIFS to show an example of how the criteria section would look to reference the current month. The BOLD portion is going to be what specifies the current month and year without having to manually update.


    =COUNTIFS([Range to Count]:[Range to Count], "specific text", [Date Column]:[Date Column], AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

  • Hello Andree - I actually don't mind as long as I can get it to work.


    Hello Paul - Thanks for this. I will need to play around with this to get it to work for me. I appreciate the help.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @TeeM Happy to help! 👍️


    You should not have to edit the bold portion at all.

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    @TeeM

    I saw that Paul answered already!

    Let me know if I can help with anything else!

    Best, 

    Andrée 

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • TeeMTeeM
    edited 03/16/20

    @Paul Newcome

    Hello Paul, I am still a bit stuck on your formula. I have selected the range to count which would be [ORDER DATE]

    I am not sure what the "specific text" would be or the [Date Column] as I thought that would be the [ORDER DATE] column

    The 'Created Date' column is not relevant in this instance.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    That is correct. In my solution above, you would need to edit the non-bold portions to fit your needs. If you don't need to specify a text string, then you can remove that part. If your date column has a different name, then you would need to update that section to reflect the correct column name.


    The bold portion is the part that was the main focus on how to specify current month and year without having to manually update every month.


    If all you want is to count order dates, then you would modify the formula to look something like this...


    =COUNTIFS([Order Date]:[Order Date], AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))


    Which leaves the criteria portion intact and modifies the non-bold portion to fit your needs.

  • @Paul Newcome Hello Paul, that has worked on the source sheet as I wanted. Thank you.

    If in the scenario where i wanted to have this formula reporting from a separate sheet i.e. the formula is on a separate report/sheet to the source data, what would I change on your formula in order for it to work? I tried just referencing the range as per your formula and I couldn't get it to work. On the Source Sheet Summary, it was fine.

Sign In or Register to comment.