0

I am using SharePoint online. My final goal is to show a calculated value.

So what I am trying is: SUM all values in one column if the ID of the item is the same. It is a list of documentations and informations about how long it took to create it.

There are several 100 documentations for one Item. The information about how long it took to create it is always different. How can I show the summed up time for every item?

It is only possible to show the summed up time behind every single item right?

Would it be possible to use a SUMIF function with conditions from other lists?

Here you see my List1. Items can show up several time. There is a calulated time for every item. Now I want to sum all time values if the item ID is the same. But I don't want them to be summed up in List1 but List2.

enter image description here

Here is my List2, where I have an overview of the items I have. Here I would like to show the summed up times from List1 by item.

enter image description here

Thanks for your help.

2
  • Are you using SharePoint Online or On-Premise? And if you can provide more information about the relationship between items and documentations will be helpful (Screenshot if possible). – Jerry_MSFT Nov 16 '20 at 8:16
  • Hi Jerry, thanks for your fast response. I am using SPO. I updated my question. – simon1608 Nov 16 '20 at 10:21
0

first, sorry when I am wrong with answering my own question. But I don't know how to add Screenshots within a comment to an answer...

So here are the screenshots of what I tried to built with your help. What do I need to fill into the add-function instead of "Apply_to_each"? "Leistungserbringung gesamt Z" is not working here.

enter image description here

enter image description here

enter image description here

Just trying to get this right:

1.) The Flow will always be triggered when a new item in my list "Tagesdokumentation_TEST" will be created.

2.) Always the variable "gesamt_IST" will be initialized (does that mean the variable will be ready to calculate with?)

3.) Gets all items with the same value in column "KlientInY", so the flow can use it in the next steps. (In my list "KlientInY" is a lookup column from another list)

4.) Compose-function should define that all times (in column "Leistungserbringung gesamt Z") with the same value for KlientInY should be summed up in column "Gesamt IST"???

5.) The variable is defined in step 4 but now the output of step 4 is used to set the variable?

1
  • Hi, I made an update on the original reply, have a check – Jerry_MSFT Nov 18 '20 at 8:27
0

Calculated column is not able to do your work. In SharePoint Online I will suggest using MS Power Automate(used to called as Flow) to do so. I build a test list and MS flow to get the column sum with condition.

My list: enter image description here

My flow: enter image description here enter image description here

The formula:add(variables('Total Time'),items('Apply_to_each')?['SpentHour'])

After this, we have the calculated sum value. It is stored in the variable Total Time. You can use it to update the list you need.

Update

If we need to filter on a lookup column, we need to change the filter query as

<Field Internal Name>/Title eq <Field Internal Name> value

Like this: enter image description here

2
  • Hello Jerry_MSFT, in my case the "TeamName" is a lookup column. Could it be a reason that MS Flow don't show me an "Apply to each" when I add "Compose"? And do you use "Number" when initialize variable? – simon1608 Nov 17 '20 at 9:54
  • @simon1608 For lookup column things may be a bit different. I will test again to see what changes needed to be done on the flow – Jerry_MSFT Nov 18 '20 at 7:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.