Is this scenario possible?
Im trying to find out if the possible scenario is possible in smartsheet:
Im trying to create a time based resource model with input from a questionnaire.
I have the resource requirements in 1 data sheet (ie what resource does it take to complete a list of activities for an item)  Sheet 1 below
i have a 2nd sheet that will tell me how many quarters it takes to complete the activity  Sheet 2 below
im trying to find out if i can combine both sheets to give a scenario where i can map out resource requirement for each quarter?
i tried to use the question number as my unique identified but as it changes with each row, im unable to do a column formula
Sheet 1:
Sheet 2:
Answers

Hi @HuiXian
I can't quite tell how your data is being mapped in your sheet 1, so please forgive me if I've misunderstood your question!
It sounds like potentially you could use a formula on your second sheet that counts how many quarters are associated with the value. Since you have halfquarters you would want each cell to equal 2, and a halfcell to equal 1. (e.g. AD1T1 would equal 5).
=(COUNTIF([Q1]@row:[Q6]@row, <>"") * 2)  COUNTIF([Q1]@row:[Q6]@row, CONTAINS("0.5", @cell))
Then you could get a SUM of AD1T1 data from sheet 1 and divide it by that number to see how much is needed per quarter:
=SUMIFS({Column to Sum}, {Column with unique Question Number}, [Question Number]@row) / [Quarters Formula]@row
Once you know what the resource is broken down by 0.5 of a quarter, you can use SUMIFS in the top row for your totals per quarter, like so:
=(SUMIFS([SUM formula]:[SUM formula], [Q1]:[Q1], <>"", [Q1]:[Q1], NOT(CONTAINS("0.5", @cell))) * 2) + SUMIFS([SUM formula]:[SUM formula], [Q1]:[Q1], CONTAINS("0.5", @cell))
This applies 2x to the SUM value that we found for any cell without 0.5 in the column, and only 1x the value for 0.5 cells.
Let me know if this makes sense and provides you with the type of summary you're looking for!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Hi Genevieve
You gave me an interesting perspective  let me explore this!
For Sheet 1  it is being populated via a Form. Form provides a response and response looks up the value from another sheet.
Thank you!
Cheers
HuiXian
Help Article Resources
Categories
Check out the Formula Handbook template!