Add data based on project owner?

Morris R

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

Protonsponge

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.

Andrée Starå

https://community.smartsheet.com/discussion/122292/add-data-based-on-project-owner

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!

Protonsponge

Are you looking for something like this?

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

Protonsponge

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

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

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

dojones

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

@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

@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

Protonsponge

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.

Protonsponge

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

@Protonspounge Thank you

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

Thank you

Morris R

So previously i couldn't get this formula to work but have been trying off and on trying to understand why it didnt work.

I finally discovered that since i have a formula feeding my "minutes" i cant use the Sum formula above (=SUM(INDEX(COLLECT({Morris Sheet 1 - Minutes), (Morris Sheet 1 - Project Owner), "Morris"), 0)|

Didnt realize this was a limitation.

Is there anything i can do to work around this?

Morris R

To clarify i am using another formula to get the "minutes" calculated and then using this formula (above provided by @Protonspounge in another column. its returning zeros

however if i remove the first formula and use @Protonspounge formula as intended then it works which isnt ideal.

Morris R

Hello,

This worked as i mentioned above, curious if i could take one step further?

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

I have another column (STATUS) that is status of projects (singular drop down) curious if it can collect above minutes only if the drop down has "SCOPING" and "INFLIGHT"

Thank you again