Year formula

I have a formula that is working that is counting the # of quotes that have been won by month which was working for 2023 now that its 2024 I want to show 2023 separate and start new for January 2024. The formula that Im used for 2023 is below. I believe that I just need to reflect the year into my countif formula and tried a couple of things but its not working for me.


=COUNTIFS([Date Quote Won]:[Date Quote Won], IFERROR(MONTH(@cell), 0) = 1, [Quote Status]:[Quote Status], "Won")

Best Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    edited 01/06/24 Answer ✓

    Hello @TPALJA

    Just add YEAR() to your formula similar to how you have MONTH() as a criteria. Swap out 2023 with 2024 or other years as needed.

    =COUNTIFS([Date Quote Won]:[Date Quote Won], IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell),0) = 2023, [Quote Status]:[Quote Status], "Won")


    👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!

    PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hey @TPALJA

    This is all correct for how to filter and add your criteria... however there's no column listed to SUM! 🙂

    Try adding the column to sum at the beginning of the formula:

    =SUMIFS([Forecast Amount]:[Forecast Amount], [Date Won]:[Date Won], IFERROR(YEAR(@cell), 0) = 2023, [Forecast Amount]:[Forecast Amount], >=1, [Quote Status]:[Quote Status], "Won")


    Cheers,

    Genevieve

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hey @TPALJA

    If you have two separate sheets, you'll need to create two separate COUNTIFS and then add them together for a total count:

    =COUNTIFS(sheet 1) + COUNTIFS(sheet 2)

    Try:

    =COUNTIFS({Archived Lead Follow Up & Incentive Tracker Range 2}, IFERROR(YEAR(@cell), 0) = 2023, {Archived Lead Follow Up & Incentive Tracker Range 2}, IFERROR(MONTH(@cell), 0) = 1) + COUNTIFS({Lead Follow Up & Incentive Tracker New Range 1}, IFERROR(YEAR(@cell), 0) = 2023, {Lead Follow Up & Incentive Tracker New Range 1}, IFERROR(MONTH(@cell), 0) = 1)

    Cheers,

    Genevieve

Answers

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

    Hi @TPALJA

    I hope you're well and safe!

    Try something like this.

    =COUNTIFS([Date Quote Won]:[Date Quote Won], IFERROR(MONTH(@cell), 0) = 1, [Date Quote Won]:[Date Quote Won], IFERROR(YEAR(@cell), 0) = 2024, [Quote Status]:[Quote Status], "Won")

    Did that work/help?

    I hope that helps!

    Have a fantastic weekend & Happy New Year!

    Best,

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

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. 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:[email protected] | P: +46 (0) - 72 - 510 99 35

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

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    edited 01/06/24 Answer ✓

    Hello @TPALJA

    Just add YEAR() to your formula similar to how you have MONTH() as a criteria. Swap out 2023 with 2024 or other years as needed.

    =COUNTIFS([Date Quote Won]:[Date Quote Won], IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell),0) = 2023, [Quote Status]:[Quote Status], "Won")


    👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!

    PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!

  • TPALJA
    TPALJA ✭✭

    Another question on Sumifs. Looking for the forecast amount that was won in 2023. Im using the Date won column, Forecast amount and Quote status of Won. Below is the formula that im trying


    =SUMIFS([Date Won]:[Date Won], IFERROR(YEAR(@cell), 0) = 2023, [Forecast Amount]:[Forecast Amount], >1, [Quote Status]:[Quote Status], "Won")


  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hey @TPALJA

    This is all correct for how to filter and add your criteria... however there's no column listed to SUM! 🙂

    Try adding the column to sum at the beginning of the formula:

    =SUMIFS([Forecast Amount]:[Forecast Amount], [Date Won]:[Date Won], IFERROR(YEAR(@cell), 0) = 2023, [Forecast Amount]:[Forecast Amount], >=1, [Quote Status]:[Quote Status], "Won")


    Cheers,

    Genevieve

  • TPALJA
    TPALJA ✭✭

    @Genevieve P. Im glad to know that i was so close to getting it. It worked perfectly thank you!

  • TPALJA
    TPALJA ✭✭

    @Genevieve P. I'm using two sheets to update my data, which are Archived Lead Follow Up & Incentive Tracker Range 2 and Lead Follow Up & Incentive Tracker New Range 1 but the formula below isn't working. I ultimately want to know the total # of jobs that started by each month for 2023 using the two sheets.

    =COUNTIFS({Archived Lead Follow Up & Incentive Tracker Range 2}, {Lead Follow Up & Incentive Tracker New Range 1}, IFERROR(YEAR(@cell), 0) = 2023, {Archived Lead Follow Up & Incentive Tracker Range 2}, {Lead Follow Up & Incentive Tracker New Range 1}, IFERROR(MONTH(@cell), 0) = 1)

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hey @TPALJA

    If you have two separate sheets, you'll need to create two separate COUNTIFS and then add them together for a total count:

    =COUNTIFS(sheet 1) + COUNTIFS(sheet 2)

    Try:

    =COUNTIFS({Archived Lead Follow Up & Incentive Tracker Range 2}, IFERROR(YEAR(@cell), 0) = 2023, {Archived Lead Follow Up & Incentive Tracker Range 2}, IFERROR(MONTH(@cell), 0) = 1) + COUNTIFS({Lead Follow Up & Incentive Tracker New Range 1}, IFERROR(YEAR(@cell), 0) = 2023, {Lead Follow Up & Incentive Tracker New Range 1}, IFERROR(MONTH(@cell), 0) = 1)

    Cheers,

    Genevieve

  • TPALJA
    TPALJA ✭✭

    @Genevieve P. COUNTIFS using two sheets worked perfectly!! thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!