Sum of closed opportunities by current quarter, current year and year to date

I previously used salesforce, and enjoyed many features of the program, but sales slowed down as I had a slow down in sales for a few years and salesforce was too expensive to pay for and not use.

I 'm trying to modify the Sales/Pipeline Template Metrics sheet to show me "Closed Won" Opportunities for the following time frames:

  1. Current Quarter
  2. Year to Date
  3. Last 12 months

These formulas are over my head as I'm just getting into using metric sheets, reports, dashboards etc.


Any help would be greatly appreciated!


Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Joshua_Will

    I think the easiest thing to do here would actually be to create 3 Reports instead of use formulas. You could use formulas, but Reports have built in functionality that will create simple calculations, such as SUM, to help you see overall totals as well.

    You would use the Filter portion of a Report to create your Timeframes and identify the "Closed - Won" criteria, then you can use the Summarize function to create a header row.


    There is potentially one formulas I would add to make filtering easier. You can use a helper column in the source sheet to simply pull the YEAR from your date, like so:

    =YEAR([Closed Date]@row

    Then you can use this helper column in your Report to filter by 2022.


    Current Quarter will be a bit trickier, as it depends on what your quarters are defined as. We could do a formula, or you could use the specific Date Range in a Report:


    Cheers,

    Genevieve

  • Thank you for the help Genevieve,

    I looked into running a report for the information needed, but the reports don't appear to be as graph friendly on the dashboard as pulling data from a Metrics Sheet.

    I ended up putting together the following formulas:

    Year to Dater by Quarter:

    =SUMIFS({Sheet - Sales Pipeline Range 2}, {Sheet - Sales Pipeline Range 4}, "Closed Won", {Sheet - Sales Pipeline Range 3}, AND(@cell >= DATE(YEAR(TODAY()), 1, 1), @cell < DATE(YEAR(TODAY()), 4, 1)))


    and Previous Year by Quarter:

    =SUMIFS({Sheet - Sales Pipeline Range 2}, {Sheet - Sales Pipeline Range 4}, "Closed Won", {Sheet - Sales Pipeline Range 3}, AND(@cell >= DATE(YEAR(TODAY() - 365), 1, 1), @cell < DATE(YEAR(TODAY() - 365), 4, 1)))


    I have one row per quarter and a parent row for totals:


    I've got my original question figured out unless there is a more efficient way to do these equations.


    One thing I am stuck on is pulling the same data for the current week only. I cannot for the life of me get the equation right, see below:

    =SUMIFS({Sheet - Sales Pipeline Range 2}, {Sheet - Sales Pipeline Range 4}, "Closed Won", {Sheet - Sales Pipeline Range 3}, AND(@cell >= TODAY() - WEEKDAY(TODAY()) - 6, @cell <= TODAY() - WEEKDAY(TODAY())))

    I believe this is pulling data back 6 days from today rather beginning of this week only.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Joshua_Will

    You're right, if the end-goal is to have this information in a Chart, then a Report would need to have both Grouping and Summary applied in order to be the source for the Chart.

    Your first SUMIFS formulas look great! For your "Current Week" formula, what about using the WEEKNUMBER Function? You can sum together any of the rows that have a date with the same Weeknumber as today:

    =SUMIFS({Sheet - Sales Pipeline Range 2}, {Sheet - Sales Pipeline Range 4}, "Closed Won", {Sheet - Sales Pipeline Range 3}, IFERROR(WEEKNUMBER(@cell), "") = WEEKNUMBER(TODAY()))

    Cheers,

    Genevieve

  • Trang Nguyen
    Trang Nguyen ✭✭✭✭

    @Joshua_Will how would you alter your quarterly formulas to report previous quarters. I'm trying to build dashboards that report actuals so we would only have actuals for previous months and quarters. Thanks in advance for your help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!