Complex Formula COUNTIFS for Last Month

Options

I have 2 working formulas that I wish to combine.

The first is a count where MSD Data field = Yes - it works and gives me a result of 65

=COUNTIFS([MSD Product]:[MSD Product], ="Yes")

The second is a complex formula with dates (that this community helped me construct genius that they are) which gives me a count in the previous calendar month i.e January - you can see why I needed help !!!

=COUNTIFS([Raised on]:[Raised on], AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)))


What I need to achieve, and cannot seem to get to work, is where the MSD Data field = Yes and the Raised On field was last month.

I've tried adding the MSD part to the front then a comma then the raised on etc. I have also tried adding the MSD field to the end and after another AND but doesn't work.

so for example this doesn't work and gives #unparseable error

=COUNTIFS([Raised on]:[Raised on], AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)) AND ([MSD Product]:[MSD Product], ="Yes"))


I have passed my ability to compute this and really hope that you can help.

Sue Rogers

AmerisourceBergen - MWI Animal Health

Business Analyst

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 02/04/21 Answer ✓
    Options

    Hi @SueinSpain

    Hope you are fine, please try the following formula.

    =COUNTIFS([MSD Data field]:[MSD Data field], "yes", [Raised On]:[Raised On], MONTH(@cell) = (MONTH(TODAY()) - 1))

    the following screenshot is from the sample sheet i create for your case


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 02/04/21 Answer ✓
    Options

    Hi @SueinSpain

    Hope you are fine, please try the following formula.

    =COUNTIFS([MSD Data field]:[MSD Data field], "yes", [Raised On]:[Raised On], MONTH(@cell) = (MONTH(TODAY()) - 1))

    the following screenshot is from the sample sheet i create for your case


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • SueinSpain
    SueinSpain ✭✭✭✭✭
    Options

    You are an absolute genius.

    I have used this, adapted this and it works brilliantly thank you


    For a new challenge :) I want to do something very similar with =SUMIFS but again cannot get the right number of commas to work so obviously being stupid (again!)


    I am trying to add up the [Value of Goods] for last month. I have ...... but get #unparseable

    =SUMIFS([Value of Goods]:[ Value of Goods], [Raised on]:[Raised on], MONTH(@cell) = (MONTH(TODAY()) - 1))

    Just cannot see where I am going wrong so hope you can help with this one too.

    Sue Rogers

    AmerisourceBergen - MWI Animal Health

    Business Analyst

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

    Hi @SueinSpain

    Try something like this.

    =SUMIFS([Value of Goods]:[ Value of Goods], [Raised on]:[Raised on], MONTH(@cell) = MONTH(TODAY()) - 1)


    This was wrong in yours.

    =SUMIFS([Value of Goods]:[ Value of Goods], [Raised on]:[Raised on], MONTH(@cell) = (MONTH(TODAY()) - 1))


    Did that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) 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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • SueinSpain
    SueinSpain ✭✭✭✭✭
    Options

    no still #unparseable

    It is in a summary sheet if that makes any difference?

    Sue Rogers

    AmerisourceBergen - MWI Animal Health

    Business Analyst

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

    @SueinSpain

    Do you mean Sheet Summary and not another sheet?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • SueinSpain
    SueinSpain ✭✭✭✭✭
    Options

    I think the rain is getting to me - yes sorry in the Sheet Summary for a single smartsheet

    I previously used your brilliant =COUNTIFS to get me total number of records for previous month (3 months & 12 months - you are a genius)

    =COUNTIFS([Raised on]:[Raised on], AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)))

    Then needed to epand to count if a value in a field is met and record in the last month - got there with help again this time from Bassam!

    =COUNTIFS([MSD Product]:[MSD Product], "Yes", [Raised on]:[Raised on], AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)))


    But now instead of a count I need the sum of a [Value of Goods] field.

    Can do the grand total so all field contents OK. just have troubles doing it for the last calendar month. Thinking of deleting users :) so they don't keep asking for these difficult solutions.

    Sue Rogers

    AmerisourceBergen - MWI Animal Health

    Business Analyst

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

    @SueinSpain

    Haha! No worries!

    Thanks for the kind words.

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • SueinSpain
    SueinSpain ✭✭✭✭✭
    Options

    have shared on the sheet - the correct one 2nd time around :)

    Sue Rogers

    AmerisourceBergen - MWI Animal Health

    Business Analyst

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!