countifs with a date criteria

04/30/19 Edited 12/09/19

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)


Popular Tags:


  • Nic LarsenNic Larsen ✭✭✭✭✭

    Give this a try: 

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

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

  • Nic LarsenNic Larsen ✭✭✭✭✭

    No problem - happy to help. Glad it worked. 

  • Paul NewcomePaul 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.

Sign In or Register to comment.