-
How do you create an "AverageIf" formula for more than 1 criteria in the same column?
Hi Team, I am using a formula calculation sheet to try to work out how to create a formula to average %s in a Smartsheet where the Line of Business Column (in some cases) has more than 1 Business unit e.g. Sales and Sales (Local Sales). I have tried Average If functions, AvgCollect, AverageAnd, but have had no luck. I just…
-
How do I reference another sheet for a specific value and it must return another cell value?
For example: Source Sheet Immediate Action Sheet What I am trying to do in the Immediate Action Sheet is: If 'Require Immediate Action' column in the Source Sheet is a "Y", I want Case ID to populate along with the User ID. Immediate Action Sheet - Final result
-
Auto Numbering
I'd like to write a formula that auto numbers each parent row in a sheet, advancing one number from the prior row. I would ideally like to set this up as a column formula so that each time I enter a new row of data, the number advances to the next instance without having to copy the formula. I would only like the next…
-
Display #NO MATCH as other value
I have a vlookup that will sometimes not return a value and instead returns #NO MATCH. I'm trying to create a helper column to translate that #NO MATCH into helpful text for the end user, like "Not Found on Verizon Invoice" to indicate the row exists on our internal tracking sheet but not on our company invoice. I can't…
-
How to: From a specific number to allocate a contact?
We have assigned numbers for each department. And I want to make a formula or automation that if there are some specific numbers they will be assigned to a specific team member/contact - as to remove the need to do this manually or to ask them to do this. New to Smartsheet. Thank you
-
Column formula for only Children rows with Parent row =SUM(CHILDREN())
I have PARENT and CHILDREN rows with the column where for PARENT row I indicate "TOTAL" in separate column. I would like to create Column Formula to calculate "Estimated Total" either for PARENT raw or for CHILDREN row automatically.
-
Formula works unless there is no date and then it returns the dreaded #Invalid data type.
=IF(Helper@row = "1", "January", IF(Helper@row = "2", "February", IF(Helper@row = "3", "March", IF(Helper@row = "4", "April", IF(Helper@row = "5", "May", IF(Helper@row = "6", "June", IF(Helper@row = "7", "July", IF(Helper@row = "8", "August", IF(Helper@row = "9", "September", IF(Helper@row = "10", "October", IF(Helper@row…
-
Get the column or collection index of a Matched value
In the example below I am trying to get the array index of the matched value. I'm looking for the number in columns Jan - Dec where it equals the value in Current Month. I can find the value, but I need the position so I can derive the financial period the entry falls into. Or better yet, how would I get the position of…
-
How do we apply COUNTIFs with multiple conditions?
I Have 5 Columns namely Phase 1, Phase 2, Phase 3, Phase 4, Phase 5 I need a count of each row item (Less than 100%) with a condition that if a task is less than 100% in multiple columns like phase 3 and phase 4, it should be only counted in the first column (Phase 3 in this example)
-
Return the highest and 2nd highest value
Hello, I need a formula which will return the highest and 2nd highest value. For the table below, it would need to return "Wednesday" as the highest and "Tuesday" as #2.
