Year formula

TPALJA

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

SoS | Dan Palenchar

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

Genevieve P.

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.

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

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!

SoS | Dan Palenchar

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

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.

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

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

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.

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

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