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.
Comments
-
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.
-
That worked great Paul, thank you.
-
-
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.
-
Which column would be blank?
-
Column is called Loss Reason
-
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 -
So which column would have the blank you are trying to count? Loss Reason or Quote Stage?
-
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.
-
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.
-
Ah ok, thanks again.
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 439 Global Discussions
- 138 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!