# Formula to sort by date based on another criteria

Options
edited 12/09/19

Hello

I am looking for a formula that will tell me how many times a certain criteria comes up per month. We have a quote log that has different stages and we need to know how many of each type of quote stage exist per each month.

In the attached screenshot I need to know how many quotes were "Develop" in the quote stage column per month in the "Requested Due Date" column.

Tags:

• ✭✭✭✭✭✭
Options

Try something like this...

=COUNTIFS([Quote Stage]:[Quote Stage], "Develop", [Requested Due Date]:[Requested Due Date], IFERROR(MONTH(@cell), 0) = ##)

Replace the ## with whatever month number you are looking for.

• Options

That worked great Paul, thank you.

• ✭✭✭✭✭✭
Options
• Options

One more request with this formula. How would I make it tally how many cells are blank, as in how many are yet to be filled. We want to see how many records are not filled in so we can fill in the blanks.

• ✭✭✭✭✭✭
Options

Which column would be blank?

• Options

Column is called Loss Reason

• ✭✭✭✭✭✭
Options

You would just add that range and criteria to the same formula then.

=COUNTIFS([Quote Stage]:[Quote Stage], "Develop", [Requested Due Date]:[Requested Due Date], IFERROR(MONTH(@cell), 0) = ##, [Loss Reason]:[Loss Reason], @cell = "")

• Options

I think I didn't explain exactly what I am looking for. Instead of the Develop I want to see how may of the cells are blank.

The Develop criteria is part of a drop down menu:

Closed - Lost

Closed - Won

Develop

Negotiate

Closed - No Bid

Regretted On Bid

Revisions

• ✭✭✭✭✭✭
Options

So which column would have the blank you are trying to count? Loss Reason or Quote Stage?

• Options

It will be Loss Reason. I am trying to apply a similar formula to another column, just didn't want to confuse the question by asking about another column when the formula is similar.

• ✭✭✭✭✭✭
Options

Ah. Ok. I thought you were trying to add another column to the formula.

You would use the same syntax. Just replace the old column names with the new column names and the old criteria with the new criteria.

=COUNTIFS([Loss Reason]:[Loss Reason], "", [Requested Due Date]:[Requested Due Date], IFERROR(MONTH(@cell), 0) = ##)

Replace the ## with whatever month number you are looking for.

• Options

Ah ok, thanks again.

• ✭✭✭✭✭✭
Options

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!