Total Sum By Month selected on Dropdown

Options
cramsey
cramsey
edited 12/09/19 in Formulas and Functions

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. 

Total by Month.JPG

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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.

  • cramsey
    Options

    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. 

     

    Months dropdown.JPG

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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)

  • cramsey
    cramsey
    edited 03/29/19
    Options

    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))))

    Not yet.JPG

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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)

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Happy to help! (but this time I did nothing) wink

    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.

  • cramsey
    Options

    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. 

    Smartnow.JPG

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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?

  • cramsey
    Options

    Yes. That is correct. That is exactly what I'm seeking. yes

     

    Kindly assist how to perform this.smiley 

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Try typing this in exactly as is...

     

    =SUMIFS(CHILDREN(),CHILDREN(Date@row), IFERROR(MONTH(@cell), 0), @cellHelper@row)

  • cramsey
    Options

    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. 

    Helper formula.JPG

    Proof of formula.JPG

    invalid Operations.JPG

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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.

  • cramsey
    cramsey
    edited 04/11/19
    Options

     

        It worked :) laugh     Thank You Sir!!

     

     

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Excellent! Sorry for the mix up, but glad it's working for you now. yes

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!