Newbie Question - simple formula help

AcDavi
AcDavi ✭✭
edited 12/29/23 in Formulas and Functions

Hi all - I'm trying to create a formula to automatically divide a $amount by 4 IF the accrual column is listed as "all 2024".

OR a more complicated formula: ideally id like to cross reference sheets to SUM all $amounts for Accrual: Q1 AND Accrual "All 2024/divided by 4" . Is it possible to do this?

total $ requested for Q1 = Any "Q1" requests AND any "All 2024"/4(to break amount into quarters) requests

Any help would be GREAT!

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You would use a standard SUMIFS for the Q1 entries. Then you would add to it another standard SUMIFS for the All 2024 entries divided by 4.


    =SUMIFS({Column To Sum}, {Quarter Column}, @cell = "Q1 2024") + (SUMIFS({Column To Sum}, {Quarter Column}, @cell = "All 2024") / 4)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide some screenshots for context?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • AcDavi
    AcDavi ✭✭

    @Paul Newcome Hi - Yes!

    Bottom Pic: Q1 requested Column. I want to get a formula that will Total all purchase amounts that belong to the following criteria: Accrual: Q1 & 1/4 of All 2024 (4 quarters, divide total by 4), Marketing Campaign: Channel and Sub task: Channel - Other.

    I would create a formula for each row as the criteria changes for Marketing Campaign & Sub Task.


    I can create the formula correctly if i just have Q1 but I dont know how to include the rows that are listed as "all 2024" and have them break down into correct amounts while adding it to all of the other Q1 totals.


    Help! Hopefully this makes sense?


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You would use a standard SUMIFS for the Q1 entries. Then you would add to it another standard SUMIFS for the All 2024 entries divided by 4.


    =SUMIFS({Column To Sum}, {Quarter Column}, @cell = "Q1 2024") + (SUMIFS({Column To Sum}, {Quarter Column}, @cell = "All 2024") / 4)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • AcDavi
    AcDavi ✭✭

    @Paul Newcome I think it's working!! You are awesome! thank you for the help

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!