Formula to sort by date based on another criteria

NLegresley

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. 

 

Paul Newcome

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.

NLegresley

That worked great Paul, thank you. 

Paul Newcome

Happy to help! 

NLegresley

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. 

Paul Newcome

Which column would be blank?

NLegresley

Column is called Loss Reason

Paul Newcome

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 = "")

NLegresley

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

Paul Newcome

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

NLegresley

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. 

Paul Newcome

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.

NLegresley

Ah ok, thanks again. 

Paul Newcome

Happy to help!