Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
Date range in formula to consolidate monthly totals for other sheets
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
Best Answer
-
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)
Answers
-
The issue is actually with the bit after the dates starting with the Price Tier range. What is that part supposed to be doing?
-
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.
-
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)
-
Perfect!! as always you're a legend, Paul, and your smartsheet advice is as epic as your beard.!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 208 Use Cases
- 517 Announcements
- 5.6K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 84 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!