Total Sum By Month selected on Dropdown
I need to be able to select to view the total cost of children by a certain month.
Currently, I have the dropdown values of all 12 months. I have the formula to the right of the dropdown calculating the total cost recorded for each child. But based on a different month, I want the value to change based on the date recorded for each child.
Cost entered under Parent as Children recorded by different dates.
Comments
-
Hi,
Can you describe your process in more detail and maybe share the sheet(s) or some more screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)
Have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
There are a few ways to do this. I would suggest adding a helper column that will convert January to 1, February to 2, March to 3, so on and so forth.
You could then use a formula such as the one below.
=SUMIFS(CHILDREN([Cost Column]@row), CHILDREN([Date Column]@row), IFERROR(MONTH(@cell), 0) = [Helper Column]@row)
This will look at the helper column which has the numeric value for the month selected in your dropdown and sum up the children in your cost column whose month is the same as what is in the helper column. The IFERROR accounts for blank rows in the date column as blanks would give you an error.
-
Thanks Guys,
Almost there. What would be the Helper Formula?
So far, I came up with =VALUE([Column21]6 ("January", 1)) >>>>Which doesn't work
So, what formula will work in the Help Column with all 12 months?
=VALUE([Column21]6 ("January", 1))("February", 2))("March", 3)) >>>>>>Dummy formula but trying provide insight in my thoughts.
-
There are two options.
1. A nested IF's statement along the lines of...
=IF([Column21]@row = "January", 1, IF([Column21]@row = "February", 2, IF([Column21]@row = "March", 3, IF([Column21]@row = "April", 4, .........................))))))))))))
2. Two additional columns to build out a chart.
Month Value
January 1
February 2
March 3
April 4
.............
Then the formula of =INDEX(Value:Value, MATCH([Column21]@row, Month:Month, 0))
.
Both will work.
You could also just build the table and forget about pulling the Value into a helper and just drop that directly into your SUMIFS (the same could be done with the nested IF's statement).
=SUMIFS(CHILDREN([Cost Column]@row), CHILDREN([Date Column]@row), IFERROR(MONTH(@cell), 0) = insert nested IF's or INDEX/MATCH formula here)
-
Not yet
Monthly Invoice (Formula) *Still not working
=SUMIFS(CHILDREN([Total Cost]12,CHILDREN(Date12, IFERROR(Month11), 0) =Helper11
Helper Formula *works
=IF(Month@row = "January", 1, IF(Month@row = "February", 2, IF(Month@row = "March", 3, IF(Month@row = "April", 4))))
-
Ok. I am a little confused by your layout. Maybe it's because I can't see the actual row numbers or the primary column that would show me what is indented as a child row. I also see some inconsistencies in the row numbers of your formula, and the syntax is a little off.
Here's what you have typed above...
=SUMIFS(CHILDREN([Total Cost]12,CHILDREN(Date12, IFERROR(Month11), 0) =Helper11
Here's what you show in your screenshot...
=SUMIFS(CHILDREN([Total Cost]12,CHILDREN(Date12, IFERROR(Month110, 0) =Helper11
.
First you need to make sure that you are closing out your CHILDREN functions...
CHILDREN([Total Cost]12)
CHILDREN(Date12)
.
Next, make sure your row references are correct. If the parent row is row 11, then that's what should be referenced in your CHILDREN function.
.
In your IFERROR statement you are referencing a column titled Month. The formula should be using the MONTH function instead. MONTH(date to pull month from)
.
Because we are looking at a range with the MONTH function being the criteria, we need to specify that we only want to look at months that are equal to a certain value.Thus we use the @cell reference.
MONTH(@cell)
.
Returning to row references...
If the formula is on the actual parent row, you can use the @row reference to avoid accidentally typing in a wrong number.
.
Try using this formula exactly as is. The ONLY changes that should be made are row references and that is only if the formula is not on the parent row itself.
=SUMIFS(CHILDREN([Total Cost]@row),CHILDREN(Date@row), IFERROR(MONTH(@cell), 0), Helper@row)
.
If the formula is in the parent row in the [Total Cost] column, you can even do away with that cell reference to get
=SUMIFS(CHILDREN(),CHILDREN(Date@row), IFERROR(MONTH(@cell), 0), Helper@row)
-
Happy to help! (but this time I did nothing)
Have a fantastic weekend!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi Paul,
My apologies as I believe that I did not explain this correctly.
The formula you presented : =SUMIFS(CHILDREN([Total Cost]@row),CHILDREN(Date@row), IFERROR(MONTH(@cell), 0), Helper@row)
This did not work and I believe I understand why.
Per the screen shot attached- The goal is when a user selects a new "month" in the drop down column, the monthly invoice needs to sum (equal) the total referenced for the amounts "Total Cost" by the dates "date" that match.
Example, May should show total for items that are listed for that month 05/08/19
Your =SUMIFS brought back and error so i adjusted SUMIFS to SUMIF and all it did was add the helper number the Monthly Invoice.
I hope this clears up any misunderstanding.
-
SUMIF and SUMIFS actually have different syntax, so it would have to be written differently other than just removing the S.
To use your screenshot... In the row that you have the arrows in, if a user were to select February, it should actually return a 0, but January should return $26.00? March and April would both also return a 0, but May would return $13.00?
-
Yes. That is correct. That is exactly what I'm seeking.
Kindly assist how to perform this.
-
Try typing this in exactly as is...
=SUMIFS(CHILDREN(),CHILDREN(Date@row), IFERROR(MONTH(@cell), 0), @cell = Helper@row)
-
Via screenshot, I've received an error again. I've also displayed both formulas. I've noticed in your most recent formula it did not account for the "total cost" row.
I feel like we are almost there. I took a few days to investigate before replying to you again.
-
My apologies. I gave you a bad formula. Somehow I managed to jack up the syntax. Here is the corrected one.
=SUMIFS(CHILDREN(),CHILDREN(Date@row), IFERROR(MONTH(@cell), 0) = Helper@row)
.
As far as not referencing the Total Cost column, I assumed that the formula would be going in the parent row of that column. If the formula is in that column, that it is assumed to be CHILDREN([Total Cost]@row). If it is NOT in that column, then you will need to specify.
-
It worked Thank You Sir!!
-
Excellent! Sorry for the mix up, but glad it's working for you now.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!