countifs with a date criteria

drodgers

I am trying to create a countifs formula that would count "New Quotes" with a  January date while it's referencing another sheet.

New Quotes would be in  the New Quote/Repricing column of this spreadsheet and then there's the Proposal date column that is in a MM/DD/YY format.

=COUNTIFS({CPI Prospects 2019 Range 1}, "New Quote", {CPI Prospects 2019 Range 2}MONTH(@cell),01)

 

Nic Larsen

Give this a try: 

=COUNTIFS({CPI Prospects 2019 Range 1}, "New Quote", {CPI Prospects 2019 Range 2}, IFERROR(MONTH(@cell), 0) = 1)

drodgers

You are my new best friend. Thank you so much!!!

Nic Larsen

No problem - happy to help. Glad it worked. 

Paul Newcome

To expand on why this worked as opposed to your original formula:

 

Most immediately, you were missing a comma between your second range and criteria.

 

Secondly, if there are any blank cells or cells containing non-date data within your range, it will throw an error within the MONTH(@cell) function which in turn would cause the entire formula to fail. That is the reason for wrapping the MONTH(@cell) in the IFERROR statement.