-
COUNT(DISTINCT(COLLECT to count unique values based on criteria
Hi, I tried using the COUNT(DISTINCT(COLLECT function to determine how many unique values based on a Date column. I need to count the unique Batch Numbers that were Received on Date = TODAY. This is the formula I came up with: =COUNT(DISTINCT(COLLECT({Batch Number}, {Date: Received}, TODAY()))) It returns 1, which I know…
-
Count with criterion.criteria within multi-select cell
I have multi-select cells in a sheet. I can use countm to count all selected items in a specific cell. However I need to count specific selections based on criteria. For example: A dropdown contains: L1-ABC, L1-LMN, L1-XYZ,L2-ABC,L2-LMN,L2-XYZ, L3-ABC,L3-LMN,L3-XYZ One cell has: L1-ABC,L2-XYZ,L2-ABC,L3-XYZ I want to search…
-
Need IF statement help
I am trying to build a formula using Duration to auto-select one of four statuses. Below is the formula I'm trying to build: If Duration is less than/equal to 30 days, then = "Transition" OR Duration is greater than 30 days and less than/equal to 60 days, then = "Reshaping", OR Duration is less than/equal to 90 days, then…
-
Counting Dates within a Date column
Hi all, I'm trying to set up a formula that counts specific dates within a date column, using the following approach: =COUNTIF({Reference 1}, "31-12-2020") It keeps returning 0 when there are least 4 dates within the target column that match the specified criteria. Can anyone advise how I can fix this? Many thanks in…
-
Percentage within two rows
Can someone help me with a formula to determine what % is "Reps in NV" of the "Total Reps"?
-
Outlier Formula
I have a column labeled "Month Tenure" that has an outlier for of 215 which are the months that a staff member's been with the company, this staff member is on column labeled "Agents". Can someone please help me create a formula for this outlier?
-
Can you update 100+ Auto-Notifications without going one by one?
I have 100+ Auto-Notifications set up between two Smartsheets. These notifications have specific due dates that change on a quarterly basis. They are set to send on the specific dates that we input. I am looking for an easy way to update all of these notifications each quarter rather than going one by one to update them in…
-
Trying to change a logo/color scheme to existing worksheets and forms
Hi, I've had a change in logo and colors and can change it on my account, but can't seem to change it on existing workspaces or worksheets and their forms. I became the admin for the account and all its documents when other people left, so I should have rights to do what is needed. Thanks, Sarah
-
Formula fails after 1000 rows
Have this formula that works like a dream until I get to 1000 rows It basically creates a sequential number like this DM-0998, DM-0999. ="DM-" + IF(One@row < 10, "000", IF(One@row < 100, "00", IF(One@row < 1000, "0", One@row))) + JOIN(COLLECT(One@row:Two@row, One@row:Two@row, ISNUMBER(@cell)), ".") Once we hit 1000 rows…
-
Formula to count the number of characters after a symbol
Hello. I am attempting to write a formula that will count the number of characters that follow a "-", and use the resulting number to determine if the type should be a "PO" or a "SWA" using an IF statement. I found a formula that will pull those characters following the "-", but it just gives me the actual 2 numbers…