Add data based on project owner?

Options
Morris R
Morris R ✭✭✭✭
edited 05/21/24 in Formulas and Functions

Hello,

Hoping for a simple add of data based on another columns drop down selection.

Looking to add up my minutes based on when my name is selected

Looking to do this from another sheet

Please and thank you

Best Answer

  • Protonspounge
    Protonspounge ✭✭✭✭✭
    edited 05/28/24 Answer ✓
    Options

    To bring the result in from another sheet… in my example I set it up like this -

    Sheet 1 - Note name of sheet is "Morris Sheet 1" as I showed above…

    For my Sheet 2 ("Morris Sheet 2") , were I am going to bring in the answer from Morris Sheet 1 is set up like this:-

    I noted in your formula above =SUMINDEXICOLLECT (Reference Sheet Minutes), (Reference Sheet Proiect Owner), "Morris", 0)| you are missing the curly brackets {} to indicate referencing another sheet. I hope I am not over explaining but to try and help you as much as I can, I have detailed my example step by step below.

    When setting this formula up from sheet 2 (Morris Sheet 2) type =INDEX(COLLECT( in your cell, and then you want to click Reference Another Sheet in the drop down box.

    In the dialogue box, once you have located the reference sheet (Morris Sheet 1), select the Minutes column. (I renamed the Sheet reference "Morris Sheet 1 - Minutes). Then click insert reference

    In your sheet 2 (Morris Sheet 2) you should have something like this:-

    Click on Reference Another Sheet again, and this time select the Project Owner column. (I renamed the Sheet reference "Morris Sheet 1 - Project Owner). Then click insert reference.

    Back in sheet 2 (Morris Sheet 2), if you finish the formula off with , "Morris"), 0)) you should have what you are looking to achieve.

    =SUM(INDEX(COLLECT({Morris Sheet 1 - Minutes}, {Morris Sheet 1 - Project Owner}, "Morris"), 0))

    I hope this helps to get you going, it definitely works in the test sheets I made.

Answers

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

    Hi,

    I hope you're well and safe!

    Not sure I follow!

    Can you elaborate and share some screenshots? (Please delete/replace any confidential/sensitive information before sharing.) That would make it easier to help.

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

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

    Did my comment help or answer your question? Please support the Community and me by marking it 
    💡 ⬆️ ❤️ or/and as the accepted answer. It will make it easier to find a solution! Thanks!

    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.

  • Protonspounge
    Protonspounge ✭✭✭✭✭
    Options

    Are you looking for something like this?

    =SUMIF([Project Owner]:[Project Owner], "morris", Minutes:Minutes)

  • Protonspounge
    Protonspounge ✭✭✭✭✭
    Options

    I missed your second point about doing this from another sheet. To do this from another sheet you could use index/collect as detailed below

    If your {Reference Sheet} looked like this:-

    You could set up you "Another Sheet" with this formula -

    =SUM(INDEX(COLLECT({Reference Sheet_Minutes}, {Reference Sheet_Project Owner}, "Morris"), 0))

  • Morris R
    Morris R ✭✭✭✭
    Options

    Hello - that is exactly what i needed but it didnt work

    I reviewed it multiple times and didnt add data returned "0"

  • dojones
    dojones ✭✭✭✭
    Options

    Another approach is to setup another sheet with individual names at title of every column and in the first column like below.

    Use this formula

    =SUMIFS({Reference Sheet Minutes}, {Reference Sheet Project Owner}, [Mark Black]$1)

    =SUMIFS({Reference Sheet Minutes}, {Reference Sheet Project Owner}, [Gary White]$1)

  • Morris R
    Morris R ✭✭✭✭
    Options

    @dojones thank you

    Already set up the sheet to managed the drop down section since the sheet is set horizontally to manage individual tasks.

    thank you though for the support

  • Morris R
    Morris R ✭✭✭✭
    Options

    @Protonspounge

    =SUMIF([Project Owner]:[Project Owner], "morris", Minutes:Minutes)

    Returning "0" vs adding the minutes

    =SUMINDEXICOLLECT (Reference Sheet Minutes), (Reference Sheet Proiect Owner), "Morris", 0)|

    Returning "0" as well

    I tried variations of each and unfortunately not showing anything

  • Protonspounge
    Protonspounge ✭✭✭✭✭
    Options

    Hello @Morris R, Sorry that I missed your previous comment, I didn't spot your reply until todays message.

    Not quite sure why this isn't working for you. Lets start with getting the calc to work in the same sheet…

    In my test sheet I had it set up like this and in the column [Morris Minutes], I had the following formula -

    =SUMIF([Project Owner]:[Project Owner], "Morris", Minutes:Minutes)

    Do you have this formula in a new column (e.g. where I have [Morris Minutes]).

    I have the [Project Owner] column set as a drop down column like you suggested and it seems to be working well for me.

  • Protonspounge
    Protonspounge ✭✭✭✭✭
    edited 05/28/24 Answer ✓
    Options

    To bring the result in from another sheet… in my example I set it up like this -

    Sheet 1 - Note name of sheet is "Morris Sheet 1" as I showed above…

    For my Sheet 2 ("Morris Sheet 2") , were I am going to bring in the answer from Morris Sheet 1 is set up like this:-

    I noted in your formula above =SUMINDEXICOLLECT (Reference Sheet Minutes), (Reference Sheet Proiect Owner), "Morris", 0)| you are missing the curly brackets {} to indicate referencing another sheet. I hope I am not over explaining but to try and help you as much as I can, I have detailed my example step by step below.

    When setting this formula up from sheet 2 (Morris Sheet 2) type =INDEX(COLLECT( in your cell, and then you want to click Reference Another Sheet in the drop down box.

    In the dialogue box, once you have located the reference sheet (Morris Sheet 1), select the Minutes column. (I renamed the Sheet reference "Morris Sheet 1 - Minutes). Then click insert reference

    In your sheet 2 (Morris Sheet 2) you should have something like this:-

    Click on Reference Another Sheet again, and this time select the Project Owner column. (I renamed the Sheet reference "Morris Sheet 1 - Project Owner). Then click insert reference.

    Back in sheet 2 (Morris Sheet 2), if you finish the formula off with , "Morris"), 0)) you should have what you are looking to achieve.

    =SUM(INDEX(COLLECT({Morris Sheet 1 - Minutes}, {Morris Sheet 1 - Project Owner}, "Morris"), 0))

    I hope this helps to get you going, it definitely works in the test sheets I made.

  • Morris R
    Morris R ✭✭✭✭
    Options

    @Protonspounge Thank you

    Yes it worked - missed a detail but your explanation helped!

    Thank you

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!