Countif specific type and year

Meredith Rhodes

=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

JamesB

@Meredith Rhodes

Try this.

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

Darren Mullen

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

Meredith Rhodes

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

JamesB

Just add another criterion to your countifs statement.

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

Meredith Rhodes

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

JamesB

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

Meredith Rhodes

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

Meredith Rhodes

@JamesB and @Darren Mullen

=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?

Meredith Rhodes

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

JamesB

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

Paul Newcome

https://community.smartsheet.com/discussion/comment/387162#Comment_387162

@Meredith Rhodes

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

Meredith Rhodes

Thank you @Paul Newcome!

Paul Newcome

Happy to help. 👍️

Melissa Torrez

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

Paul Newcome

@Melissa Torrez Your criteria would be

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