working with forecasting and actual sales amounts

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}, "[email protected]", {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
-
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
Categories
Check out the Formula Handbook template!