Date range in formula to consolidate monthly totals for other sheets

Chris Martin

Help would be appreciated.

I have multiple sheets with order details in them. I would like to create a sheet that I can have monthly totals side by side for each SKU and each pricing tier.

So I thought I would just try referencing one sheet to make sure it worked before adding the other sheets, but when it come to date ranges I'm stuck (well I think that is my problem)

As you can see I'm getting an error every time I try something.

If someone could help me or better yet help me craft a formula that I don't need to hard code the range for every month (ie help me future proof it).

Thanks so much

Chris

Paul Newcome

Ok. That's where the syntax issue is actually coming from (not the date section). You will need to move one of the closing parenthesis from the end to after the PARENT function to close off the SUMIFS before moving on to the third portion of the IF statement.

You are also going to want to use a cell reference inside of the PARENT function. Without a cell reference, it pulls in the parent of the column the formula is in which is going to be blank. I imagine you are probably going to want

PARENT([Price Tier]@row)

although that cell is also blank, so it may be

PARENT([Primary Column]@row)

Paul Newcome

The issue is actually with the bit after the dates starting with the Price Tier range. What is that part supposed to be doing?

Chris Martin

Hi Paul,

it was just another function of the sumifs. so purpose of whole function is that it only adds together the quantities of sku's that are in the desired date range and of the price tier of the parent row.

Paul Newcome

Ok. That's where the syntax issue is actually coming from (not the date section). You will need to move one of the closing parenthesis from the end to after the PARENT function to close off the SUMIFS before moving on to the third portion of the IF statement.

You are also going to want to use a cell reference inside of the PARENT function. Without a cell reference, it pulls in the parent of the column the formula is in which is going to be blank. I imagine you are probably going to want

PARENT([Price Tier]@row)

although that cell is also blank, so it may be

PARENT([Primary Column]@row)

Chris Martin

Perfect!! as always you're a legend, Paul, and your smartsheet advice is as epic as your beard.!!

Paul Newcome

Happy to help. 👍️