Formula to sort by date based on another criteria

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.

Find more posts tagged with

Formulas

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…