Create Month Over Month Reporting

PCS

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!!

Devin Lee

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

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

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

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

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

Is your Date Received column formatted as a date?

Brandon Sills

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

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

PCS

Hey Brandon,

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

Brandon Sills

Can you post screenshots of your sheets with the formulas?

Brandon Sills

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

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

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

Thank you

PCS

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

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.