-
Date formula
Hello! I currently have 2 columns on my sheet, one where I enter date processed and date expired which has a formula to calculate 1 year expiration from date processed. I have been asked that no matter what date I process, it needs to expire the last date of that month. So for example, date processed = 9/5/23 date expired…
-
Convert to Column Formula Error
I'm trying to make the below formula a column formula. If I copy and paste the formula into cells, it works as expected. =IF(OR([Status]1 = "Complete", [Status]1 = ""), "", "Yes") Two issues, though: 1) The [Status]1 brackets are being automatically removed and reformatted as Status1 2) When I try to convert to a column…
-
Count & Sum Distinct Values based on Criteria | COUNT(DISTINCT(COLLECT(...) | Returning 1 ???
In a ROLLUP sheet, referencing a source sheet, I'm trying to count (and sum) DISTINCT values that meet multiple criteria. My formula keeps returning "1", when in the shown scenario below has "80" distinct values. Hoping I'm just missing something simple. Rollup sheet purpose = count the distinct values rented within…
-
I am identifying the duplicates in the sheet. What wrong with this query?
=RANKEQ([Request ID]@row, COLLECT([Request ID]:[Request ID], [Item Number (SUPC)]:[Item Number (SUPC)], [Item Number (SUPC)]@row, Duplicates:Duplicates, 1), 1)
-
Using the RYG "Yes, Hold, No Decision" and want to calculate based on staff assigned to
Our sheet has a status column with the RYG "Yes, Hold, No Decision" icons. There is another column labled "Staff" that lists which team the tasks is assigned to. I'd like to, in the Sheet Summary, Have a count and/or percentage of each yes, hold, or no decision for each type of staff selection available in that columns…
-
Formula IFS return values from other columns
Hi, i am trying to set a new column that should return EAS or ETS text depending of what it is in column named "ETS", but i want to add another if that return a value from column "Business Dropdown" if the column ETS is blank. This is the formula that i wrote now very simple and it is running well, but it is missing the…
-
Cumulative Column Query for Sheet A and Sheet B
Hello, I have Sheet A, which contains a column with work dates that have been inspected. I would like to create a cumulative column in Sheet B based on the maximum date from Sheet A. However, it appears that the current setup is cumulating all the dates in Sheet B instead of considering the maximum date from Sheet A. I…
-
Count Collect Contains formula
I'm trying to create a metric sheet to compile results from a number of survey questions. For questions where there is one answer per question, this formula works fine: =COUNT(COLLECT({Survey Intake Name}, {Days Remote}, [Answer1]3)) {Survey Intake Name}is the base value to count, {Days Remote} is the reference to the…
-
Permutation Generator
Hey Community - I couldn't score enough time at the formulas booth this week to pick someone's brain on this one. I am looking to re-create an Excel tool that I use to create all permutations from a two column matrix, for example, if I have two columns called SIZE and SHAPE, the next column should be a column formula…
-
How to count cells when they have multiple values selected, and I'm only wanting a specific one?
Because we can't use wildcards on the program, I am at a loss to set up a report that summarizes my sheet. I'm trying to determine how many entries are the 'Status' outlined in each Primary Column row, where the 'Suggest Solution' is the text in each column header. The 'Status' can only be one value, but the 'Suggested…
