Create Month Over Month Reporting

Options

Hello,

I'm looking for the correct formula that pulls in closed won jobs comparing month over month.

I have a sales pipeline sheet with all the closed won sales.

I have a metric sheet that has all the totals for the jobs won.

I need to pull in a metric that shows the closed won sales jobs for each month and then be able to compare them to the previous month. Ideally, I would like to do this quarter over quarter and year over year. Is this possible?

How would I create this referencing the sales pipeline and then showing in a report or dashboard?

Thanks!!

«1

Answers

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Options

    You may need to convert these to sheet reference ranges but here is a general formula you can use. The idea being you can set the start and end to your range and it will pull the total for everything between those dates. That way you can determine the ranges specifically.

    =SUMIFS([Estimate Total]:[Estimate Total], [Date Received]:[Date Received], >=[Range Start Date]@row, [Date Received]:[Date Received], <=[Range End Date]@row)
    

  • Brandon Sills
    Brandon Sills ✭✭✭✭
    Options

    One way to do this:

    Formulas:

    Total Closed Won: =COUNTIF({Sales Pipeline - Sales Rep}, [Sales Rep]@row)

    Estimate Total: =SUMIFS({Sales Pipeline - Estimate Total}, {Sales Pipeline - Sales Rep}, [Sales Rep]@row)

    Jan 2024: =IF([Hierarchy Helper]@row = 1, SUMIFS({Intake - Qty}, {Intake Range 2}, [Sales Rep]@row, {Intake - Date Received}, @cell >= DATE(2024, 1, 1), {Intake - Date Received}, @cell <= DATE(2024, 1, 31)), IF([Hierarchy Helper]@row = 2, SUM(CHILDREN()), ""))

    Jan 2024 $: =IF([Hierarchy Helper]@row = 1, SUMIFS({Intake Range - Estimate Total}, {Intake Range 2}, [Sales Rep]@row, {Intake - Date Received}, @cell >= DATE(2024, 1, 1), {Intake - Date Received}, @cell <= DATE(2024, 1, 31)), IF([Hierarchy Helper]@row = 2, SUM(CHILDREN()), ""))

    For Additional Months: Change the Date Range (Year, Month, Day)

    2024 Q1: =[Jan 2024]@row + [Feb 2024]@row + [Mar 2024]@row

    2024 Q1 $: =[Jan 2024 $]@row + [Feb 2024 $]@row + [Mar 2024 $]@row

    As for the Hierarchy Helper, this enables the ability to utilize a single column formula with multiple calculations based on the Hierarchy of the row. In this case, the parent row's calculation is =SUM(CHILDREN())

    This will give you the data to build your metrics off of.

    Hope this helps.


    Brandon

  • PCS
    PCS ✭✭
    Options

    Than you both for your assistance on this!

    I am confused onthis part though: Intake Range - Estimate Total}, {Intake Range 2}, [Sales Rep]@row, {Intake - Date Received}

    What is the intake sheet that is being referenced? Is that the sales pipeline? Sorry, I'm really new at using Smarthseets and setting up this reporting is way over my head at this point. :)

  • Brandon Sills
    Brandon Sills ✭✭✭✭
    Options

    Ah, sorry! I originally had the sheet named Intake before i changed it to Sales Pipeline. See below.

    Formulas:

    Total Closed Won: =COUNTIF({Sales Pipeline - Sales Rep}, [Sales Rep]@row)

    Estimate Total: =SUMIFS({Sales Pipeline - Estimate Total}, {Sales Pipeline - Sales Rep}, [Sales Rep]@row)

    Jan 2024: =IF([Hierarchy Helper]@row = 1, SUMIFS({Sales Pipeline - Qty}, {Sales Pipeline - Sales Rep}, [Sales Rep]@row, {Sales Pipeline - Date Received}, @cell >= DATE(2024, 1, 1), {Sales Pipeline - Date Received}, @cell <= DATE(2024, 1, 31)), IF([Hierarchy Helper]@row = 2, SUM(CHILDREN()), ""))

    Jan 2024 $: =IF([Hierarchy Helper]@row = 1, SUMIFS({Sales Pipeline - Estimate Total}, {Sales Pipeline - Sales Rep}, [Sales Rep]@row, {Sales Pipeline - Date Received}, @cell >= DATE(2024, 1, 1), {Sales Pipeline - Date Received}, @cell <= DATE(2024, 1, 31)), IF([Hierarchy Helper]@row = 2, SUM(CHILDREN()), ""))

    For Additional Months: Change the Date Range (Year, Month, Day)

    2024 Q1: =[Jan 2024]@row + [Feb 2024]@row + [Mar 2024]@row

    2024 Q1 $: =[Jan 2024 $]@row + [Feb 2024 $]@row + [Mar 2024 $]@row

  • PCS
    PCS ✭✭
    Options

    Thank you Brandon for the clarification. :)

    This is what I have for the count column:

    =IF([Hierarchy Helper]@row = 1, SUMIFS([Total Closed Won]@row, {Sheet - Closed Won Sales Rep}, {Sheet - Closed Won Date Received}, @cell >= DATE(2024, 1, 1), {{Sheet - Closed Won Date Received}, @cell <= DATE(2024, 1, 31)), IF([Hierarchy Helper]@row = 2, SUM(CHILDREN()), ""))

    However, the count cell is only showing zero.

    This is what I have for the money column:

    =IF([Hierarchy Helper]@row = 1, SUMIFS({Sheet - Closed Won Estimate Total}, {Sheet - Closed Won Sales Rep}, [Sales Rep]@row, {Sheet - Closed Won Date Received}, @cell >= DATE(2024, 1, 1), {Sheet - Closed Won Date Received}, @cell <= DATE(2024, 1, 31)), IF([Hierarchy Helper]@row = 2, SUM(CHILDREN()), ""))

    and that seems to be bringing in the entire total.

    What am I missing?

  • Brandon Sills
    Brandon Sills ✭✭✭✭
    Options

    Is your Date Received column formatted as a date?

  • Brandon Sills
    Brandon Sills ✭✭✭✭
    edited 06/05/24
    Options

    Also, you should convert the formula to column formulas.

    Right click the column header and convert to column formula.

    A column with a column formula will show (fx) symbol in the header.

  • PCS
    PCS ✭✭
    Options

    My column is a date and I was having errors when converting to a column formula. I wanted to get it correct first. :)

  • PCS
    PCS ✭✭
    Options

    Hey Brandon,

    I converted the column to the formula and now I have this:

  • Brandon Sills
    Brandon Sills ✭✭✭✭
    Options

    Can you post screenshots of your sheets with the formulas?

  • Brandon Sills
    Brandon Sills ✭✭✭✭
    Options

    The formulas for the Total Closed Won and Jan 2024 should be identical except adding in the criteria for the date ranges. Can you copy and paste that formula into the post?

    Additionally, the Estimate Total and Jan 2024 $ also should be the same except the date criteria.

  • PCS
    PCS ✭✭
    Options

    Then Jan 2024 $ Column:

    =IF([Hierarchy Helper]@row = 1, SUMIFS({Sheet - Closed Won Estimate Total}, {Sheet - Closed Won Sales Rep}, [Sales Rep]@row, {Sheet - Closed Won Date Received}, @cell >= DATE(2024, 1, 1), {Sheet - Closed Won Date Received}, @cell <= DATE(2024, 1, 31)), IF([Hierarchy Helper]@row = 2, SUM(CHILDREN()), ""))

    Jan 2024 Column:

    =IF([Hierarchy Helper]@row = 1, SUMIFS([Total Closed Won]@row, {Sheet - Closed Won Sales Rep}, {Sheet - Closed Won Date Received}, @cell >= DATE(2024, 1, 1), {{Sheet - Closed Won Date Received}, @cell <= DATE(2024, 1, 31)), IF([Hierarchy Helper]@row = 2, SUM(CHILDREN()), ""))

    Test Metrics Sheet:

    Closed Won Sheet:

    I has more info on it but these are the columns i need pulled in….

    THANKS SO MUCH!

  • Brandon Sills
    Brandon Sills ✭✭✭✭
    Options

    Can you post the formulas for Total Closed Won and Estimate Total Columns as well?

    Thank you

  • PCS
    PCS ✭✭
    Options

    oh yes. sorry about that..

    Total Closed Won

    =COUNTIF({Sheet - Sales Pipeline Range 1}, [Sales Rep]@row)

    Estimate Total

    =SUMIF({PBS Sales Pipeline Range 1}, [Sales Rep]@row, {Sheet - Sales Pipeline Range 3})

  • Brandon Sills
    Brandon Sills ✭✭✭✭
    Options

    For the Month Qty columns:

    =IF([Hierarchy Helper]@row = 1, COUNTIFS({Sales Pipeline - Sales Rep}, [Sales Rep]@row, {Sales Pipeline - Date Received}, @cell >= DATE(2024, 1, 1), {Sales Pipeline - Date Received}, @cell <= DATE(2024, 1, 31)), IF([Hierarchy Helper]@row = 2, SUM(CHILDREN()), ""))

    For the Month $ columns… I think it may be your references. I would click edit column formula and then click on the reference to the other sheet and select edit reference. From there ensure you select the correct column and insert that reference.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!