working with forecasting and actual sales amounts

Options
Pepenav
Pepenav
edited 12/09/19 in Formulas and Functions

This is the formula I am trying to activate to pull some sales data from a master sheet to a monthly summary by sales person.

=SUMIFS({Current Lead Opportunity Tracker 2019 Range 3}, {Current Lead Opportunity Tracker 2019 Range 1}, "xxxx@xxxl.xx.xx", {Current Lead Opportunity Tracker 2019 Range 8}, "2", {D&C Current Lead Opportunity Tracker 2019 Range 2 }@row,1=1,”true”, “false”)

All works except for the last part where the last range is a check box column. I want to use that to create formula that trigger whether sales $ should show in a default ie non checked= forecast or checked =actual  summary cells in another sheet.

An additional issue is that our financial YE is March, and I have now also become aware that month numbers are showing for multiple financial years, ie  "4" shows in both April 2018 and April 2019, so I need to introduce Year into the formula, I have tried a number different versions but hope someone can point me in right direct for both these issues. 

Many thanks 

Comments

  • Pepenav
    Options

    Okay I post this for any others with similar problems to resolve.

    In reference to the first issue I created a control column for the check box to deliver a true or false and then used the final range to select those rows that are true.

    =SUMIFS({Current Lead Opportunity Tracker 2019 Range 3}, {Current Lead Opportunity Tracker 2019 Range 2}, "2", {Current Lead Opportunity Tracker 2019 Range 1}, "xxxxx", {D&C Current Lead Opportunity Tracker 2019 Range 2}, "true")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!