nested if formula not calculating first portion of the forumla

BFuller

I have a formula someone helped me construct using data in a summary sheet. It is the same info in three parts. The first part is if the development status is "New training item". The second part is if the development status is "Update existing training item". And, the third is "New training; replacing existing". The three formulas are all looking for specific times in the summary section based on the assets requested. The last bit of the formula is me adding in Other Adjust touch time hours manually. So basically, if the development status is "New training item, look up all these different asset times and add them together. If development status is Update existing training item, look up all these different asset times and add them together. And if development status is New training; replacing existing, look up all these different asset times and add them together. Then, that whatever that sums to and add it to anything in the Other Adjust touch time hours manually column.

Here's my issue, it works on development statuses B and C, but not on development status A. When I have something with development status "new training item" it is not adding in the other adjust touch time manually column.

Here is a pic of my formula:

Here it is pasted below. Can you help me figure out what I did wrong and why it is not adding the Other Adjusted touch time hours manually when the development status is "new training item"?

=IF(CONTAINS("New training item", [Development status]@row), SUM(IF(CONTAINS("Audio",

[Course Development]@row), CouseDevelopmentNew#, ""), IF(CONTAINS("Animation",

Animation@row), AnimationNew#, ""), IF(CONTAINS("Announcement",

Announcement@row), AnnouncementNew#, ""), IF(CONTAINS("Presentation",

Presentation@row), PresentationNew#, ""), IF(CONTAINS("Documentation",

Documentation@row), DocumentationNew#, ""), IF(CONTAINS("Assessment",

Assessment@row), AssessmentNew#, ""), IF(CONTAINS("Survey",

Survey@row), SurveyNew#, ""), IF(CONTAINS("Video",

Video@row), VideoNew#, "")), IF(CONTAINS("Update

existing training item", [Development status]@row), SUM(IF(CONTAINS("Audio",

[Course Development]@row), CouseDevelopmentUpdate#, ""), IF(CONTAINS("Animation",

Animation@row), AnimationUpdate#, ""), IF(CONTAINS("Announcement",

Announcement@row), AnnouncementUpdate#, ""), IF(CONTAINS("Presentation",

Presentation@row), PresentationUpdate#, ""), IF(CONTAINS("Documentation",

Documentation@row), DocumentationUpdate#, ""), IF(CONTAINS("Assessment",

Assessment@row), AssessmentUpdate#, ""), IF(CONTAINS("Survey",

Survey@row), SurveyUpdate#, ""), IF(CONTAINS("Video",

Video@row), VideoUpdate#, "")), IF(CONTAINS("New

training; replacing existing", [Development status]@row), SUM(IF(CONTAINS("Audio",

[Course Development]@row), CouseDevelopmentNew#, ""), IF(CONTAINS("Animation",

Animation@row), AnimationNew#, ""), IF(CONTAINS("Announcement",

Announcement@row), AnnouncementNew#, ""), IF(CONTAINS("Presentation",

Presentation@row), PresentationNew#, ""), IF(CONTAINS("Documentation",

Documentation@row), DocumentationNew#, ""), IF(CONTAINS("Assessment",

Assessment@row), AssessmentNew#, ""), IF(CONTAINS("Survey",

Survey@row), SurveyNew#, ""), IF(CONTAINS("Video",

Video@row), VideoNew#, "")))) + [Other - Adjust touch time

hours manually]@row)

isabonita

I may be looking at this wrong - but have you tried moving that last parenthesis inside the "+"?

Are you wanting to always add in the "Other - Adjust touch time hours manually] to the total? Or just when it is a "new training item"?

BFuller

Hello, thank you for responding.

I did try to add the parenthesis to the +Other Adjust touchtime hours manually and it did not change anything. Here is a screen shot of my sheet in case that helps:

The estimated touch time hours are calculated on all three development statuses. But when I manually add hours to remove the negative number under estimated hours remaining, it won't zero out like it will for the other two statuses.

BFuller

I guess I needed fresh eyes. I rebuilt the formula this morning and it is now working. Here is the revised formula:

=IF(CONTAINS("New training item", [Development status]@row), SUM(IF(CONTAINS("Course Development", [Course Development]@row), CourseDevelopmentNew#, ""), IF(CONTAINS("Animation", Animation@row), AnimationNew#, ""), IF(CONTAINS("Announcement", Announcement@row), AnnouncementNew#, ""), IF(CONTAINS("Presentation", Presentation@row), PresentationNew#, ""), IF(CONTAINS("Documentation", Documentation@row), DocumentationNew#, ""), IF(CONTAINS("Assessment", Assessment@row), AssessmentNew#, ""), IF(CONTAINS("Survey", Survey@row), SurveyNew#, ""), IF(CONTAINS("Video", Video@row), VideoNew#, "")), IF(CONTAINS("Update existing training item", [Development status]@row), SUM(IF(CONTAINS("Course Development", [Course Development]@row), CourseDevelopmentUpdate#, ""), IF(CONTAINS("Animation", Animation@row), AnimationUpdate#, ""), IF(CONTAINS("Announcement", Announcement@row), AnnouncementUpdate#, ""), IF(CONTAINS("Presentation", Presentation@row), PresentationUpdate#, ""), IF(CONTAINS("Documentation", Documentation@row), DocumentationUpdate#, ""), IF(CONTAINS("Assessment", Assessment@row), AssessmentUpdate#, ""), IF(CONTAINS("Survey", Survey@row), SurveyUpdate#, ""), IF(CONTAINS("Video", Video@row), VideoUpdate#, "")), IF(CONTAINS("New training; replacing existing", [Development status]@row), SUM(IF(CONTAINS("Course Development", [Course Development]@row), CourseDevelopmentNew#, ""), IF(CONTAINS("Animation", Animation@row), AnimationNew#, ""), IF(CONTAINS("Announcement", Announcement@row), AnnouncementNew#, ""), IF(CONTAINS("Presentation", Presentation@row), PresentationNew#, ""), IF(CONTAINS("Documentation", Documentation@row), DocumentationNew#, ""), IF(CONTAINS("Assessment", Assessment@row), AssessmentNew#, ""), IF(CONTAINS("Survey", Survey@row), SurveyNew#, ""), IF(CONTAINS("Video", Video@row), VideoNew#, ""))))) + [Other Adjust touch time hours manually]@rowan.bradley

Thank you for your assistance!