countifs with a date criteria

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)

Find more posts tagged with

Formulas

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…