-
Averageifs
Smartsheet does not appear to contain the averageifs formula. Is there an alternative that will perform a similar function. I would like to collect an average score of a sales person based on a text field which contains the region serviced. As there may be overlap in some instances I can not use the averageif option For…
-
How do I combine all cells based on specific text found in another cell?
Hi Smartsheet community! I have a from that our team uses to rank our vendors. The team selects 1st, 2nd, and 3rd place vendors and they have the option to leave comments for each vendor they select. As the results tend to involve the same three vendors, I need to find a way to "automatically" combine the comments into one…
-
Assistance with COUNTIFS formula...(New to Smartsheet/Formulas)
Hello, I am new to Smartsheet and formulas...I need assistance with a formula (creating a formula sheet for a dashboard). My formula references another sheet and I want it to count the IN-PROGRESS and UPCOMING Training for an individual trainer. I've created a few formulas that work, but not sure how to create them to…
-
Highlight duplicate values with a criteria within multiple select column
Hello I have this system where workers report with a form their done tasks and locations for those specific tasks. I am trying to avoid the option for them to do duplicate inputs. It works like this: worker chooses a task in form cell, then the locations where he did that task. They do multiple locations per form input, up…
-
Column with a running total by location
I have a column for Address, City, and State. When entering data I am able to get a column that will count the total for each, but I want to add a column that shows the running total for each. For example in row 1 I enter data for location x the running total would show 1. Then in rows 2 and 3 I enter data for location y…
-
How to block a cell on formula
Hi, I want to know how can I lock a cell in a spatial working formula. Example: = IF (AND ({PROJECT CASE TEST Rank 7} = January1, {PROJECT CASE TEST Rank 6} = January2), {PROJECT CASE TEST Rank 8} + {PROJECT CASE TEST Rank 9} * January5, "") When I pull the formula left, right or down, the cell formula can't blocked and…
-
COUNTIF using Count(Ancestors)
I am trying to use the COUNTIFS formula to return a count of items within a date range. The issue is a large number of the rows have indented/child rows that I do not want included in the count. The formula I have tried is: =COUNTIFS({FOLLOW UP Range 1},COUNT(ANCESTORS()=0)>DATE(2021,1,1){FOLLOW UP Range…
-
MONTH/YEAR formulas based on date column
Hi there, I have a column for "Report Date", "Date received", and "Month/Year". In my "Month/Year" column, I want the report date to be written out instead of 1/6/21 as January 2021. However, sometimes our "Report Date" column is blank/has the word "Skip" written when we don't have to write a report for that row. When that…
-
Ranking values in a range based on matching a date
Hi Smartsheeters - I'm in a post-Covid brain fog and trying to work this problem. I have a list of materials. For each material, I list a warehouse, a quantity of scanning errors, and a date. I have a rank column I need to populate with the top 5 highest quantity for a given date at a given warehouse. Here's a snapshot of…
-
Multiple IFs help
I am trying to create a formula with about 10 if conditions. It works with two, but as I add the 3rd I get an error. Here's the formula =IF(AND(Publication@row = "Architectural Record"), " https://www.architecturalrecord.com/call4entries/forPublication", IF(AND(Publication@row = "BD+C"), "Word count: 500 – 1,200; 4 - 5…