SUMIFS - Multiple Formulas

12/21/18 Edited 12/09/19

Hi I am trying to calculate the revenue for each leg of an event. The drop down options are "Spring", "Fall" or "Both". Using the following statements individually I can get part of the answer:

=SUMIF(CHILDREN(Roadshow21), "Spring", CHILDREN([Forecast Amount]21))

=SUMIF(CHILDREN(Roadshow21), "Both", CHILDREN([Forecast Amount]21)) / 2

With my limited ability I can't work out a way to combine this into the information I need. In other words, To get the true vale of "Spring" I need to be able to add 50% of the revenue from anything marked as "Both".

Hope this makes sense to someone in the Community.

Thank you in advance.

CT

 

Popular Tags:

Comments

  • Andrée StaråAndrée Starå ✭✭✭✭✭
    edited 12/21/18

    Hi Chris,

    Try this.

    You just needed to add them together, so you were close.

    =SUMIF(CHILDREN(Roadshow21); "Spring"; CHILDREN([Forecast Amount]21)) + SUMIF(CHILDREN(Roadshow21); "Both"; CHILDREN([Forecast Amount]21)) / 2  

    The same version but with the below changes for your and others convenience.  

    =SUMIF(CHILDREN(Roadshow21), "Spring", CHILDREN([Forecast Amount]21)) + SUMIF(CHILDREN(Roadshow21), "Both", CHILDREN([Forecast Amount]21)) / 2

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did it work?

    Have a fantastic weekend & Happy Holidays!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Thank you so much Andrée. It worked perfectly.

    Happy Holidays to you too.

    CT

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

    Great!

    Happy to help!

    Best,

    Andrée

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Sign In or Register to comment.