Nested If Statement with Index and Collect formula (multiple reference)

SamEast

Hi,

Hoping for some guidance and help in the right direction or alternative solution.

Trying to get Estimated Total in Summary sheet to return the proper calculation of Each Site reference, which are pulled from another master sheet reference.

The Estimated Total is calculated based on Location site reference, Years, Scope type[multi-select fields].

for example:

Summary sheet it is referencing first Year 1, Tropic , Annual should return Estimate : $1500

Column: Site Reference, Years, Location , Scope Type, Estimate

Year 1, Year 2, Year 3 [Single select fields]

Location A , Location B, Location C [Single select fields]

Scope Type A, Scope Type B, Scope Type C - [Multi-select fields]

Estimate A, Estimate B, Estimate C

Alternatively, (if selected

Years = Year 1

Site Reference: Tropic

Scope Type: Annual, Semi-Annual & 3 Year Test

Estimated Total: $ 5,800 (getting errors on Summary sheet)

using reference from Master sheet

Master Sheet Reference:

Column: Year, Location , Scope Type, Estimate

Year 1, Year 2, Year 3

Location A , Location B, Location C

Scope Type A, Scope Type B, Scope Type C - [Multi-select fields]

Estimate A, Estimate B, Estimate C

On Main Summary Sheet

=IF([Scope Type]@row = "Annual", INDEX((COLLECT({Estimate Tropic A}, {Year Tropic A}, Years@row, {Location Tropic A}, [Site Reference]@row, {Scope Type Tropic A}, "Annual")), 1)IF([Scope Type]@row = "Semi-Annual", INDEX((COLLECT({Estimate Tropic A}, {Year Tropic A}, Years@row, {Location Tropic A}, [Site Reference]@row, {Scope Type Tropic A}, "Semi-Annual")), 1),IF([Scope Type]@row = "3 Year Test", INDEX((COLLECT({Estimate Tropic A}, {Year Tropic A}, Years@row, {Location Tropic A}, [Site Reference]@row, {Scope Type Tropic A}, "3 Year Test")), 1))

I am using column Estimate Location A, Estimate Location B, Estimate Location C to test that each site reference is calculating properly (like helper columns to check), but if there is a better way to include all three that would be great as well!

is there an easier way to do this? or fix this formula?

I am open to any suggestions,

Thank you!

MarceHolzhauzen

Hi @SamEast

Maybe try the following:

Change your master sheet to have fewer columns

Then, in your summary sheet, you can do one of two things,

1: Change the multi select to separate checkboxes:

Then you can use the following formula:

=IF([Annual Scope]@row = 1, SUM(COLLECT({Test 1_Estimate A}, {Test 1_Year}, Year@row, {Test 1_Scope}, "Annual", {Test 1_Site}, Site@row)), 0) + IF([Semi-Annual Scope]@row = 1, SUM(COLLECT({Test 1_Estimate A}, {Test 1_Year}, Year@row, {Test 1_Scope}, "Semi-Annual", {Test 1_Site}, Site@row)), 0) + IF([3 Year Test Scope]@row = 1, SUM(COLLECT({Test 1_Estimate A}, {Test 1_Year}, Year@row, {Test 1_Scope}, "3 Year Test", {Test 1_Site}, Site@row)), 0)

What the above does, is add the 3 scopes seperately (First bold part for Annual, Italic part for Semi Annual and last bold part for 3 Year scope)

OR

2. Keeping it the way you have it, you can use the same principle as above, but use

=IF(CONTAINS("Annual", Scope@row), SUM(COLLECT({Test 1_Estimate A}, {Test 1_Year}, Year@row, {Test 1_Scope}, "Annual", {Test 1_Site}, Site@row)), 0) + IF(CONTAINS("Semi-Annual", Scope@row), SUM(COLLECT({Test 1_Estimate A}, {Test 1_Year}, Year@row, {Test 1_Scope}, "Semi-Annual", {Test 1_Site}, Site@row)), 0) + IF(CONTAINS("3 Year Test", Scope@row), SUM(COLLECT({Test 1_Estimate A}, {Test 1_Year}, Year@row, {Test 1_Scope}, "3 Year Test", {Test 1_Site}, Site@row)), 0)

I hope this helps

MarceHolzhauzen

Hi @SamEast

What are the rules for the multi select column (Scope Type A, Scope Type B, Scope Type C - [Multi-select fields])in this formula?

Should al 3 options be selected or is it any of the options?

SamEast

hi @MarceHolzhauzen ,

thank you for response. The multi-select for Scope Type would have any one of those options or all (should add/sum all the Estimate value(s) associated with these criterias based on YEAR —+— > Location —+-- > Scope type(Single or multiple Scope Type values).

The Estimate amount is dependent on all these three different criterias: Scope type, YEAR , Location , which I have these defined and it is referencing from another smartsheet in an Estimate column.

(details in my original post above for the complete master sheet details on these options of each column Scope type, YEAR and Location …and Estimate values

please let me know if you need more details,

thank you and looking forward for your feedback and anyone who has any suggestions!

MarceHolzhauzen

Hi @SamEast

Maybe try the following:

Change your master sheet to have fewer columns

Then, in your summary sheet, you can do one of two things,

1: Change the multi select to separate checkboxes:

Then you can use the following formula:

=IF([Annual Scope]@row = 1, SUM(COLLECT({Test 1_Estimate A}, {Test 1_Year}, Year@row, {Test 1_Scope}, "Annual", {Test 1_Site}, Site@row)), 0) + IF([Semi-Annual Scope]@row = 1, SUM(COLLECT({Test 1_Estimate A}, {Test 1_Year}, Year@row, {Test 1_Scope}, "Semi-Annual", {Test 1_Site}, Site@row)), 0) + IF([3 Year Test Scope]@row = 1, SUM(COLLECT({Test 1_Estimate A}, {Test 1_Year}, Year@row, {Test 1_Scope}, "3 Year Test", {Test 1_Site}, Site@row)), 0)

What the above does, is add the 3 scopes seperately (First bold part for Annual, Italic part for Semi Annual and last bold part for 3 Year scope)

OR

2. Keeping it the way you have it, you can use the same principle as above, but use

=IF(CONTAINS("Annual", Scope@row), SUM(COLLECT({Test 1_Estimate A}, {Test 1_Year}, Year@row, {Test 1_Scope}, "Annual", {Test 1_Site}, Site@row)), 0) + IF(CONTAINS("Semi-Annual", Scope@row), SUM(COLLECT({Test 1_Estimate A}, {Test 1_Year}, Year@row, {Test 1_Scope}, "Semi-Annual", {Test 1_Site}, Site@row)), 0) + IF(CONTAINS("3 Year Test", Scope@row), SUM(COLLECT({Test 1_Estimate A}, {Test 1_Year}, Year@row, {Test 1_Scope}, "3 Year Test", {Test 1_Site}, Site@row)), 0)

I hope this helps

SamEast

Thank you @MarceHolzhauzen ! that works with some modifications in sheets.

Thank you for your help! Much Appreciated!

MarceHolzhauzen

I am so glad I could help! :-)