0

Need to calculate numbers from columns when status changes in other columns. Column one has the number of hour assigned for shift 1. Column two has status of completion, this repeats for 10 shifts. When each shift is marked complete, I want to add the number form each shifts assigned hours to a calculate column. This gives me the first assigned hours =IF([1 Status]="complete",[# of hours 1])

I am not sure how to start to add each of the consecutive hour as each status is changed to complete. Here are a couple of my attempts. It goes with out saying I am new to SharePoint. All help is appreciated

=IF([1 Status]="complete",[# of hours 1])=IF([2 Status]="complete",+[# of hours 2])


=IF([1 Status]="complete",[# of hours 1]
   ,if([1 Status],[2 Status]= "complete",[# of hours 1]+[# of hours 2]))


=if([1 Status]="complete"
    ,[# of hours 1])
    ,if([2 Status]= "complete"
        ,[# of hours 1]+[# of hours 2]))
Improve this question
3
  • Does the status change happen sequentially like [1 Status] is set Complete and then [2 Status] is set to Complete, but [4 status] can't be set before [3 Status] is set, right?
    – Matiur Rahman
    Commented Oct 27, 2020 at 12:17
  • Correct must be complete sequentially. But if one is missed I need it to continue to calculate the next one. So 1 and 2 are completed and 3 the shift is missed and then 4 is completed. I would want it to add 1, 2, & 4
    – Hardhat
    Commented Oct 27, 2020 at 12:27
  • Posted the formula.. let me know if you need any further help.
    – Matiur Rahman
    Commented Oct 27, 2020 at 15:42

1 Answer 1

Reset to default
1

See the formula and as screenshot showing test results below. I have used S1, S2, .. for the status column names and H1, H2.. for the Hour column names. You would need to replace them with your actual column names.

Also, status selection should be sequential for this to work. If it breaks, the total would be calculated to zero (0)

=IF(AND(AND(AND(AND(AND(AND(AND(AND(AND(S1="complete",S2<>"complete"),S3<>"complete"),S4<>"complete"),S5<>"complete"),S6<>"complete"),S7<>"complete"),S8<>"complete"),S9<>"complete"),S10<>"complete"),H1,IF(AND(AND(AND(AND(AND(AND(AND(AND(AND(S1="complete",S2="complete"),S3<>"complete"),S4<>"complete"),S5<>"complete"),S6<>"complete"),S7<>"complete"),S8<>"complete"),S9<>"complete"),S10<>"complete"),H1+H2,IF(AND(AND(AND(AND(AND(AND(AND(AND(AND(S1="complete",S2="complete"),S3="complete"),S4<>"complete"),S5<>"complete"),S6<>"complete"),S7<>"complete"),S8<>"complete"),S9<>"complete"),S10<>"complete"),H1+H2+H3,IF(AND(AND(AND(AND(AND(AND(AND(AND(AND(S1="complete",S2="complete"),S3="complete"),S4="complete"),S5<>"complete"),S6<>"complete"),S7<>"complete"),S8<>"complete"),S9<>"complete"),S10<>"complete"),H1+H2+H3+H4,IF(AND(AND(AND(AND(AND(AND(AND(AND(AND(S1="complete",S2="complete"),S3="complete"),S4="complete"),S5="complete"),S6<>"complete"),S7<>"complete"),S8<>"complete"),S9<>"complete"),S10<>"complete"),H1+H2+H3+H4+H5,IF(AND(AND(AND(AND(AND(AND(AND(AND(AND(S1="complete",S2="complete"),S3="complete"),S4="complete"),S5="complete"),S6="complete"),S7<>"complete"),S8<>"complete"),S9<>"complete"),S10<>"complete"),H1+H2+H3+H4+H5+H6,IF(AND(AND(AND(AND(AND(AND(AND(AND(AND(S1="complete",S2="complete"),S3="complete"),S4="complete"),S5="complete"),S6="complete"),S7="complete"),S8<>"complete"),S9<>"complete"),S10<>"complete"),H1+H2+H3+H4+H5+H6+H7,IF(AND(AND(AND(AND(AND(AND(AND(AND(AND(S1="complete",S2="complete"),S3="complete"),S4="complete"),S5="complete"),S6="complete"),S7="complete"),S8="complete"),S9<>"complete"),S10<>"complete"),H1+H2+H3+H4+H5+H6+H7+H8,IF(AND(AND(AND(AND(AND(AND(AND(AND(AND(S1="complete",S2="complete"),S3="complete"),S4="complete"),S5="complete"),S6="complete"),S7="complete"),S8="complete"),S9="complete"),S10<>"complete"),H1+H2+H3+H4+H5+H6+H7+H8+H9,IF(AND(AND(AND(AND(AND(AND(AND(AND(AND(S1="complete",S2="complete"),S3="complete"),S4="complete"),S5="complete"),S6="complete"),S7="complete"),S8="complete"),S9="complete"),S10="complete"),H1+H2+H3+H4+H5+H6+H7+H8+H9+H10,0))))))))))

enter image description here

Improve this answer
1
  • Thank you I will give it a try.
    – Hardhat
    Commented Oct 27, 2020 at 19:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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