How to use COUNTIFS Month/Year with cell displaying Month/Year

Paul G.

A report is initially downloaded into Excel and a marco places the report date into each cell in the Report Date column. The entire report is then copied into Smartsheet. I will refer to it as the report.

A "Metrics" sheet will be counting various categories from the report for each month in a biennium. In one of the first columns the user will select the first day of the beginning of the biennium. Each month in the biennium along with the year is displayed in row 4. The formula for row 4 is show in the image below.

The formula in row 5 of the metrics sheet is to display to total number of row/entries for each month. I have reviewed several similar questions on counting using CLOUNTIFS and matching the month and year. In the image below are a couple of the variations I have tried, and the formula has resulted in an error message or a 0. In the formula {RngReportMonthYear] is the column in the report which contains the date of the report, for example 11/7/22. It does not matter what day during the month the report was pulled, only the month and year.

Thank you for your time and response.

Paul

Darren Mullen

@Paul G. In your COUNTIFS criteria take out the IFERROR's and just try Month(@cell) and Year(@cell)

Paul G.

Thank you @Darren Mullen for your reply.

Based on your suggestion I tried these formulas:

=COUNTIFS({RngReportMonthYear}, MONTH(@cell) = MONTH([Month17]$4), {RngReportMonthYear}, YEAR(@cell) = YEAR([Month17]$4))

=COUNTIFS({RngReportMonthYear}, MONTH(@cell) = [Month17]$4, {RngReportMonthYear}, YEAR(@cell) = [Month17]$4)

=COUNTIF({RngReportMonthYear}, AND(IFERROR(MONTH(@cell), 0) = MONTH([Month17]$4), IFERROR(YEAR(@cell), 0) = YEAR([Month17]$4)))

=COUNTIF({RngReportMonthYear}, AND(IFERROR(MONTH(@cell), 0) = [Month17]$4, IFERROR(YEAR(@cell), 0) = [Month17]$4))

The first three continue to produce the #INVALID DATA TYPE error and the last one of course produced 0 with IFERROR. I checked and the {RngReportMonthYear} is formatted as a date column.

Suggestions?

Paul

Darren Mullen

@Paul G. looks like your are trying to extract the month and year from a text column Month17? If so that's a no can do.

Paul G.

Thanks @Darren Mullen. Any suggestions other than "No can do."

Paul

Darren Mullen

@Paul G. you just need to get the month as a number (1-12) and the year as well... You'll have to figure out the best way to work that into your current sheet.

Or, You could replace "November 2022" with a representative such as "11-2022" then you can use combinations of left(), find(), and right() to parse out the information you need.... that is to get the month and the year.

Or have a gnarly if statement with the contains() function that finds and translates the month name to a number and also use find and right to find the year from the text string..

Not trying to be snippy just trying to have you learn the concepts behind why you got the error. You were giving the month() and year() functions a text value when it requires a date type column.