Sales Formulas- Revenue by month and deal total by month

Hails
Hails
edited 12/09/19 in Smartsheet Basics

HI everyone,

I am trying to create three formulas on a sales pipeline. 

The first is I am trying to track for the month of July how many deals we have that are opportunities. Then I am trying to track for the month of July how much revenue in deals we have that are in the opportunity stage. I then want to be able to track total variance from quota for the month of July so I can see if my team is on track.

I also want to be able to track how many leads we have for the month of July. 

Can someone help with this?

Screen Shot 2019-07-03 at 1.22.48 PM.png

Screen Shot 2019-07-03 at 1.22.36 PM.png

Comments

  • Alejandra
    Alejandra Employee

    Hi Hails,

    Here are a couple of formula examples you could use to accomplish this:

    A count of the number of opportunities for the month of July:

    =COUNTIFS(Type:Type, "5 - Opportunity", [Date Column 1]:[Date Column 1], >=DATE(2019, 7, 1), [Date Column 2]:[Date Column 2], <=DATE(2019, 7, 31))

    A count of the number of leads for the month of July:

    =COUNTIFS(Type:Type, "1 - Lead", [Date Column 1]:[Date Column 1], >=DATE(2019, 7, 1), [Date Column 2]:[Date Column 2], <=DATE(2019, 7, 31))

    A sum of revenue for opportunities in the month of July:

    =SUMIF(Type:Type, "5 - Opportunity", Revenue:Revenue)

    For more information on these functions, please see: