# Year formula

Options
✭✭✭

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")

• ✭✭✭✭✭✭
edited 01/06/24 Answer ✓
Options

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")

School of Sheets (Smartsheet Partner)

If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

• Employee
Options

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

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

• Employee
Options

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

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

• ✭✭✭✭✭✭
Options

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: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.

• ✭✭✭✭✭✭
edited 01/06/24 Answer ✓
Options

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")

School of Sheets (Smartsheet Partner)

If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

• ✭✭✭
Options

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")

• Employee
Options

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

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

• ✭✭✭
Options

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

• ✭✭✭
Options

@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)

• Employee
Options

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

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

• ✭✭✭
Options

@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!