COUNTIFS Statement with dates and other criteria

aecross

Trying to count how many design starts are projected for each month within the Western region. Below is my formula; however, it doesn't seem to be functioning properly. I am referencing another sheet and I've confirmed that the references are pointed at the correct columns.

=COUNTIFS({Summary.DesignStart}, IFERROR(MONTH(@cell, 0) = 1, IFERROR(YEAR(@cell), 0) = 2026), {Summary.Region}, "Western")

jmyzk_cloudsmart_jp

Hi @aecross

Your formula needs a minor improvement.

Since the COUNTIFS function takes combinations of ranges and criteria, you need to use a date range for each criterion, or use the AND function to combine the criteria.

COUNTIFS(range1, criterion1, [range2, criterion2, ...])

https://help.smartsheet.com/function/countifs

https://app.smartsheet.com/b/publish?EQBCT=803bc9db9c674cb380f7d937e6bc4841

In the COUNTIFS sheet below,

the first formula uses the date range for each month and year criteria.

In the second formula, the AND function combines the month and year criteria.

The third and fourth formulas first limit the date range with the ISDATE function.
As you can see from the Summary sheet above, the DesignStart column range contains non-date or blank cells starting in row 11. The non-date cells cause an error when you reference them in date functions like the MONTH or YEAR.

By placing the ISDATE function first, the subsequent criteria evaluate only date values, eliminating errors.

https://app.smartsheet.com/b/publish?EQBCT=7d853cb9ac9546919f4a221398db560d

=COUNTIFS(
  {Summary.DesignStart}, IFERROR(MONTH(@cell), 0) = 1, 
  {Summary.DesignStart}, IFERROR(YEAR(@cell), 0) = 2026, 
  {Summary.Region}, "Western")

=COUNTIFS(
  {Summary.DesignStart}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2026), 
  {Summary.Region}, "Western"
)

=COUNTIFS(
  {Summary.DesignStart}, ISDATE(@cell), 
  {Summary.DesignStart}, MONTH(@cell) = 1, 
  {Summary.DesignStart}, YEAR(@cell) = 2026, 
  {Summary.Region}, "Western"
)

=COUNTIFS(
  {Summary.DesignStart}, ISDATE(@cell), 
  {Summary.DesignStart}, AND(MONTH(@cell) = 1, YEAR(@cell) = 2026), 
  {Summary.Region}, "Western"
)

I think your IFERROR(MONTH(@cell ), 0) = 1 is a creative use of the IFERROR function!