Countif date range and checkbox
I need to count all of the contracts won & lost by month. Similarly I need to sum all of the proposal budgets won/lost by month.Does someone have a formula that would work for this?
Answers
-
Hi Amy,
Will you be doing this in another sheet, or in the sheet summary for this sheet?
In another sheet, you could create a Month column with the month numbers (January = 1, February = 2, etc.), then:
In the count for contracts lost column:
=COUNTIF({Date Lost Range}, IFERROR(MONTH(@cell), 0) = Month@row)
In this scenario, you've named the Date Lost column as {Date Lost Range} in a cross-sheet reference. You would do the same thing for the contracts won column, except that it would be a different range.
Then in the sum for contracts lost column:
=COUNTIF({Date Lost Range}, IFERROR(MONTH(@cell), 0) = Month@row, {budget})
Where the {budget} is the named range for the budget column of your source sheet. Again, you could do the same thing for the contracts won - just use the {date won range} instead of lost.
In a sheet summary, you could create a formula for each month, where the number in bold represents the number of the month:
=COUNTIF([Date Lost]:[Date Lost], IFERROR(MONTH(@cell), 0) = 1)
=SUMIF([Date Lost]:[Date Lost], IFERROR(MONTH(@cell), 0) = 1, Budget:Budget)
Hope this helps! Let me know if you have any questions.
Best,
Heather
-
Thank you so much Heather! The CountIf formula you suggested worked great! However, we get an unparsable error with the SumIf formula. Any suggestions? The name of the column we need to sum is "Proposed Budget".
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!