# Countif specific type and year

✭✭✭✭

=COUNTIFS(Type:Type, ="CTA New"), ([Date Submitted]:[Date Submitted], IFERROR(YEAR(@cell), 0) = 2022)

I've been looking at examples of this question all day - but for some reason I can't make my formula work.

The above formula is #Unparseable.

I want to count the number of rows with "CTA New" Type and 2022 Date Submitted Year.

Can you tell me where I'm going wrong with my formula? Do I need an AND function?

Thank you!

Meredith

Tags:
«1

• ✭✭✭✭✭✭

Try this.

=COUNTIFS(Type:Type, "CTA New", [Date Submitted]:[Date Submitted], IFERROR(YEAR(@cell), 0) = 2022)

• ✭✭✭✭✭✭

@Meredith Rhodes Remove the parentheses after "CTA New" and before [Date Submitted].

=COUNTIFS(Type:Type, ="CTA New", [Date Submitted]:[Date Submitted], IFERROR(YEAR(@cell), 0) = 2022)

• ✭✭✭✭

Thank you @Darren Mullen and @JamesB !

Smartsheet formulas really are a new language.

I have another related question. I want to divide this count by the current month we're in - so that I can get an average monthly 'CTA New' per the specific year.

This is relevant to the current year, of course. This is July = 7. So - if I make this formula (as you recommend above) for 2023:

=COUNTIFS(Type:Type, ="CTA New", [Date Submitted]:[Date Submitted], IFERROR(YEAR(@cell), 0) = 2023)/7

Is there a way to reference the current month of the year in the denominator?

Thank you!

Meredith

• ✭✭✭✭✭✭

=COUNTIFS(Type:Type, ="CTA New", [Date Submitted]:[Date Submitted], IFERROR(YEAR(@cell), 0) = 2022, [Date Submitted]:[Date Submitted], IFERROR(MONTH(@cell), 0) = 7 )

• ✭✭✭✭

@JamesB - I think that would give me a count rather than an average. I'd like the formula to update in the Sheet Summary so I always see a monthly average.

Such that the denominator is 7 for July - but changes to 8 for August.

• ✭✭✭✭✭✭
edited 07/26/23

@Meredith Rhodes - If I am reading this correctly, you want to get the total for YTD and then average it by the number of months YTD?

I am thinking you want something like this.

=SUM(COUNTIFS(Type:Type, "CTA New", [Date Submitted]:[Date Submitted], IFERROR(YEAR(@cell), 0) = 2023) / COUNTIFS(Type:Type, "CTA New", [Date Submitted]:[Date Submitted], IFERROR(MONTH(@cell), 0) = MONTH(TODAY())))

• ✭✭✭✭

Thank you for this insight @JamesB !

It looks like =MONTH(TODAY()) returns the correct number (7), but the overall equation returns a slightly different average.

The following equation seems to work :)

=COUNTIFS(Type:Type, "CTA New", [Date Submitted]:[Date Submitted], IFERROR(YEAR(@cell), 0) = 2023) / MONTH(TODAY())

Thanks again!

Meredith

• ✭✭✭✭

=COUNTIFS(Type:Type, "CTA New", [Date Submitted]:[Date Submitted], IFERROR(YEAR(@cell), 0) = 2023) / MONTH(TODAY())

works this year...

BUT - after this year - hmmmm unless I replace the denominator with '12', will it keep changing? (I think so)

SO, what is the logic that will tell it - in the denominator - that if the year is greater than 2023, return 12?

• ✭✭✭✭

Will something like this work for logic?

=COUNTIFS(Type:Type, "CTA New", [Date Submitted]:[Date Submitted], IFERROR(YEAR(@cell), 0) = 2023) / (IF(YEAR(TODAY()) = 2023), (MONTH(TODAY()), 12))

The above equation is #UNPARSEABLE

• ✭✭✭✭✭✭

@Meredith Rhodes Trying to understand. Are you wanting the rolling logic to include from 2023 as you roll into 2024? If not, you should be able to replace 2023 in the equation to YEAR(TODAY()) and then it will always look at the current year based on your computers clock.

=COUNTIFS(Type:Type, "CTA New", [Date Submitted]:[Date Submitted], IFERROR(YEAR(@cell), 0) = YEAR(TODAY())) / MONTH(TODAY())

• ✭✭✭✭✭✭

This should take care of capping it at 12 when we roll into the next year:

=COUNTIFS(Type:Type, "CTA New", [Date Submitted]:[Date Submitted], IFERROR(YEAR(@cell), 0) = 2023) / IF(YEAR(TODAY())> 2023, 12, MONTH(TODAY()))

• ✭✭✭✭

Thank you @Paul Newcome!

• ✭✭✭✭✭✭

Happy to help. 👍️

• ✭✭✭✭✭

@Paul Newcome I am trying to do something similar. I want to count the number of items that meet 2 criteria and are within the "current year"

I have a metrics sheet where I have the formulas referencing a data sheet. I want to count the number of items that have Deal Stage 5, in Segment 1 and are within the current year 2023.

• ✭✭✭✭✭✭

@Melissa Torrez Your criteria would be

IFERROR(YEAR(@cell), 0) = YEAR(TODAY())

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!