Forecast Formula

Michelle Maas

I am trying to figure out a formula for the following from a reference sheet:

{Value Column} = SUM total

{Date Column} = current YEAR, specific MONTH

{Primary Column} = CONTAINS "Invoice" in Children to MATCH "Client Name" in Parent

{Status Column} = "Forecast"

ISBLANK if none of above match or cell is empty

Paul Newcome

It is populating zero because the syntax is incorrect and the IFERROR portion is grabbing it. Try this...

=IFERROR(SUMIFS({Value Column}, {Client Column}, [Primary Column]@row, {Status Column}, OR(@cell = "Forecast", @cell = "Not Started"), {Date Column}, MONTH(@cell) = 11), 0)

Paul Newcome

Are you able to provide a screenshot of the source data for reference? More specifically the Primary Column bit?

Michelle Maas

Michelle Maas

I have a similar formula I'm attempting to tweak a previous formula, but it is adding the whole value column and missing some of the criterion i.e. Status and Client columns. Also I'm not sure if it is picking up the specific month or just adding it all together?

Original formula:

=IFERROR(SUMIFS({Sales Pipeline Range 1}, {Sales Pipeline Range 3}, [Account Name]@row, {Sales Pipeline Range 6}, MONTH(@cell) = 1), 0)

Tweaked:

=IFERROR(SUMIFS({Service and Maintenance Control Sheet Range 1}, PARENT({Service and Maintenance Control Sheet Range 2}), [Primary Column]@row, {Service and Maintenance Control Sheet Range 4}, ="Forecast", "Not Started", "True", "False", {Service and Maintenance Control Sheet Range 3}, MONTH(@cell) = 10), 0)

Paul Newcome

So hierarchy functions can't be used with cross sheet references unfortunately. That means that we will need to insert a helper column on the source sheet that will bring that client name into all of the child rows.

From there it would be a SUMIFS formula where (in addition to the her criteria) you look at the helper column for the client name.

Michelle Maas

Oh good, there is a client column that I can reference it to.

Out of interest, can Smartsheet formulas read other formulas and pick up the cell content as it reads, or does the cell need to contain only text in order for a formula to work?

Also for the Primary Column, I need it to pick up the word "Invoice", as in the formula CONTAINS, to ensure that when users put additional words in that column, the formula is still recognising it.

=IFERROR(SUMIFS({Value Column}, {Client Column}, [Primary Column]@row, {Primary Column} = "Invoice", {Status Column} = "Forecast", "Not Started", {Date Column}, MONTH(@cell) = 11), 0)

This formula returns the value 0.00, but there is an amount in the value column, for the month of Nov, so not sure why it is not reading it. If it was blank, then 0.00 would make sense. What am I missing?

Michelle Maas

I realised I can drop the "Invoice" criteria, as any value would sit against that row anyway. What I am particularly interested in is that the row status is Forecast or Not Started, and the date is a particular month:

=IFERROR(SUMIFS({Value Column}, {Client Column}, [Primary Column]@row, {Status Column} = "Forecast", "Not Started", {Date Column}, MONTH(@cell) = 11), 0)

This formula should return a figure that is in that referenced cell for Nov, but it is still showing as 0.00.

Paul Newcome

It is populating zero because the syntax is incorrect and the IFERROR portion is grabbing it. Try this...

=IFERROR(SUMIFS({Value Column}, {Client Column}, [Primary Column]@row, {Status Column}, OR(@cell = "Forecast", @cell = "Not Started"), {Date Column}, MONTH(@cell) = 11), 0)

Michelle Maas

That's brilliant, @Paul Newcome. It's working perfectly. Now I need to add the year, for example:

=IFERROR(SUMIFS({Value Column}, {Client Column}, [Primary Column]@row, {Status Column}, OR(@cell = "Forecast", @cell = "Not Started"), {Date Column}, YEAR(@cell) = 2024, MONTH(@cell) = 11), 0)

There should be a figure in the cell, but it is showing as 0.00. Thanks for your help with this. Really appreciate it! 😀

Michelle Maas

Also, if a column in the reference sheet i.e. Client Column is a drop down list, or the Value Column is a formula calculation, would this interfere with the reporting formula? I am getting 0.00 for these, so it seems to not be identifying the values in those referenced cells?

Michelle Maas

I GOT IT! It's now picking up the year.

=IFERROR(SUMIFS({Service and Maintenance Control Sheet Range 1}, {Service and Maintenance Control Sheet Range 5}, [Account Name]@row, {Service and Maintenance Control Sheet Range 2}, OR(@cell = "Forecast", @cell = "Not Started"), {Service and Maintenance Control Sheet Range 3}, MONTH(@cell) = 1, {Service and Maintenance Control Sheet Range 3}, YEAR(@cell) = 2024), 0)

Paul Newcome

I'm glad you got the YEAR portion working. Here is a quick tip (up to your personal preference really) that I like to do... I personally like to keep all of my criteria for the same range grouped together when possible, so you can actually use an AND statement and only enter the range once like so:

=IFERROR(SUMIFS({Service and Maintenance Control Sheet Range 1}, {Service and Maintenance Control Sheet Range 5}, [Account Name]@row, {Service and Maintenance Control Sheet Range 2}, OR(@cell = "Forecast", @cell = "Not Started"), {Service and Maintenance Control Sheet Range 3}, AND(MONTH(@cell) = 1, YEAR(@cell) = 2024)), 0)

The dropdown column could be an issue if it is a multi-select and has multiple options. The way your current formula is written it will only look for exact matches.

But it could also be the value column depending on how exactly that formula is written.

So is the dropdown a multi-select? If not, what is the exact formula in the value column?

Michelle Maas

Thanks @Paul Newcome, great tip. The way I got around these ones is I removed the Account Name section of the formula. The next formula I am working on is one where we have a weighting formula calculating two columns in a row i.e. estimated amount, less percentage possibility, equals the weighted amount. Currently the other project sheets are pulling from a Value Column which is just a numeric entry in the cell. I'll start this one on Monday, but thank you so much for your help. You have progressed my reporting much quicker than me trying on my own. Have a lovely weekend. 😁

Paul Newcome

If removing the Account Name column helped, then it almost sounds as if maybe there was something not quite lining up. I'd be happy to explore it in more detail if you'd like.