-
Calculate the right average for the columns
I need the average collect to calculate the right total % complete for the columns Each column that has a date is = 100% If there is no date is = 0% but the formula still counts the 0% to give the total % average complete on "Quote % Complete" column. If there is "N/A" either in "1st Circuit Quote Status or 2nd Circuit…
-
Does INDEX(MATCH) work on a column with a formula?
I am attempting to use the INDEX(MATCH) formula with the range being a column that is calculated with a formula. I keep getting a #NOMATCH message - can MATCH only work on a column that has actual values (i.e., not values calculated from a formula)? I am trying to use this formula: =INDEX({Billing Code}, MATCH([Identifier…
-
Need formula help.
I have the following formula: =IFERROR(IF([Date Complete]@row = [Due Date]@row, 0, IF([Date Complete]@row > [Due Date]@row, NETWORKDAYS([Due Date]@row, [Date Complete]@row) - 1, IF([Date Complete]@row < [Due Date]@row, NETWORKDAYS([Due Date]@row, [Date Complete]@row) + 1, ""))), "") How do I add a contingency for if the…
-
Subtracting 1 month from TODAY, previous year error
=DATE(YEAR(day13), MONTH(day13) - 1, DAY(day13)) I am using the above formula to subtract one month from today's date for a rolling 12 month report. However, the formula errors above 15/01/22 (I expected it to revert to 15/12/21) is there a way I can build the formula so it does not error and continues into the previous…
-
Trying to check a box using OR
I want a box to be checked IF Sales Status is "Verbal Agreement" or "Deployment Prep Call - Scheduling" or "Deployment Prep Call - Scheduled" =IF([Sales Status]@row = OR("Verbal Agreement", "Deployment Prep Call - Scheduling", "Deployment Prep Call - Scheduled", 1)) I'm getting the error INCORRECT ARGUMENT SET. Thanks for…
-
Find the average of hours, minutes, seconds
Hi there, I am having difficulties finding the average of hours, minutes and seconds. I have 12 months of data for timings, and the format is in hours, minutes, seconds how it is in excel - 00:00:00. I have to find the average for each month, for an entire years worth of data, and it gives me an error when I look for the…
-
Joining multiple COUNTIFS statements
Hi there. I'm trying to create a single formula that will count items in a column on sheet A if they fall between two dates and also count items on sheet B if they fall between two dates. The item name is the same in both sheets and the date ranges are the same in both sheets. The formula I have (which does not work) looks…
-
Why am I getting an INVALID DATA TYPE error?
Here is my formula, which references another sheet. All referenced columns are the same type (drop-down) =COUNTIFS({Lead Category}, OR(@cell = "Lite Upgrade", "@cell =New Lite Site"), {Sales Type}, "New Deployment", {Sales Tier}, "Experiencia") Thanks for your help!
-
Using a formula to subtract out a Contact
Hello, Is it possible to use a formula that when given a contact cell with multiple assigned contacts, and a secondary cell with one assigned contact, will SUBTRACT that contact from the first cell? So in practice it would look like this: Cell 1: Contact1, Contact2, Contact3 Cell2: Contact2 Cell3: Use a formula that…
-
Struggling with formula that references status in another sheet
Hello Smartsheet friends! I have a sheet (called Test Receiver Sheet) where I want a box to be checked for each row if the client code for that row matches client code on the second sheet (called TEST Client Audit Form) AND the status is "Complete". This is the formula I have so far. Im getting an invalid reference error.…