Current Month Formula

Options
Michelle Maas
Michelle Maas ✭✭✭✭
edited 03/29/23 in Formulas and Functions

I keep getting an error on the below formula trying to COUNT for the previous month. I am reporting from two sheets, the live one and an archived version. This formula works fine until I add the current month requirement, then it throws up an error for #INCORRECT ARGUMENT:

=COUNTIFS({Sales Pipeline Opportunity}, (CONTAINS("Enquiry raised", @cell)), {Archive Sales Start}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))) + COUNTIFS({Archive Sales Opportunity}, (CONTAINS("Enquiry raised", @cell)), {Archive Sales Start}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

Appreciate the help.

Best Answer

  • Michelle Maas
    Michelle Maas ✭✭✭✭
    edited 03/28/23 Answer ✓
    Options

    I realised I did not need to pick up the enquiry row, so formula now works:

    =SUMIFS({Sales Pipeline Amount}, {Sales Pipeline Start}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))) + SUMIFS({Archive Sales Amount}, {Archive Sales Start}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

Answers

  • Michelle Maas
    Michelle Maas ✭✭✭✭
    edited 03/28/23 Answer ✓
    Options

    I realised I did not need to pick up the enquiry row, so formula now works:

    =SUMIFS({Sales Pipeline Amount}, {Sales Pipeline Start}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))) + SUMIFS({Archive Sales Amount}, {Archive Sales Start}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

  • Genevieve P.
    Options

    Glad to hear that it works now! 🙂

    Thanks for sharing your solution.

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!