Nested If Statement with Index and Collect formula (multiple reference)
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!
Best Answer
-
Hi @SamEast
Maybe try the following:
Change your master sheet to have fewer columnsThen, 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
Marcé Holzhauzen
Solution and Delivery Consultant with Award Winning Partner
Want to meet?Check my availabilitywww.prodactive.co.uk
If this helped, help me & the SSC by accepting it and reacting w/💡insightful, ⬆️ Vote Up, and/or ❤️Awesome.
Answers
-
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?Marcé Holzhauzen
Solution and Delivery Consultant with Award Winning Partner
Want to meet?Check my availabilitywww.prodactive.co.uk
If this helped, help me & the SSC by accepting it and reacting w/💡insightful, ⬆️ Vote Up, and/or ❤️Awesome. -
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!
-
Hi @SamEast
Maybe try the following:
Change your master sheet to have fewer columnsThen, 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
Marcé Holzhauzen
Solution and Delivery Consultant with Award Winning Partner
Want to meet?Check my availabilitywww.prodactive.co.uk
If this helped, help me & the SSC by accepting it and reacting w/💡insightful, ⬆️ Vote Up, and/or ❤️Awesome. -
Thank you @MarceHolzhauzen ! that works with some modifications in sheets.
Thank you for your help! Much Appreciated!
-
I am so glad I could help! :-)
Marcé Holzhauzen
Solution and Delivery Consultant with Award Winning Partner
Want to meet?Check my availabilitywww.prodactive.co.uk
If this helped, help me & the SSC by accepting it and reacting w/💡insightful, ⬆️ Vote Up, and/or ❤️Awesome.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives