Current Year Specific month formula, need assistance please.

I am trying to modify a formula written by someone else that is looking in the current year for open quotes that are not closed prior to the month being pulled or have no closing date. I need to do this for each month individually for graphing our sales pipeline. The numbers are not accurate and almost appear to add over time. This is the current formula being used, would this be accurate for August? 

=SUMIFS({Total}, {Quote Date}, <DATE(YEAR(TODAY()), 9, 1), {Close Date}, >DATE(YEAR(TODAY()), 9, 1)) + SUMIFS({Total}, {Quote Date}, <DATE(YEAR(TODAY()), 9, 1), {Close Date}, ="") + SUMIFS({Total}, {Quote Date}, ="")

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭

    @Trion

    This formula is going to add together anything in the {Total} range that has a Quote Date less than 09/01 of the current year and a Close Date greater than 09/01 of the current year. Then it's going to add that total to anything in the {Total} range that has a Quote Date less than 09/01 of the current year and a Close Date that's blank. Then it's going to add to it anything in the {Total} range that has a Quote Date that's blank.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!