-
Using Countifs with Ors and Ands
I am looking for help with a formula. Hopefully there is one out there. I have several columns Month, 1 Assigned to, 2 Assigned to, 3 Assigned to, 4 Assigned to, and Status. I need to count if it is in say month 1 and assigned to office in any of the Assigned to columns and the status is open. Is there a way to have the…
-
Match any part of multi select to another multi select on another sheet
Hello, I have two separate sheets that each have multi select columns and I want to join a particular column if it has ANY mutual matches between the two. For example, on sheet 1, A B C are selected in the multi select and I want to find any matches on sheet 2 which contain anything selected from sheet 1. The column on…
-
How can I copy information to a new sheet to create a log?
I'm trying to see if there's a way to have data copied from a few specific columns to a new sheet. I'm hoping to create a log for IT issues as they occur on requests. The difficulty is setting some sort of trigger that will move the information over into a new row whenever a new issue is added. The other possibility, would…
-
Formula for Star Rating that Ignores Blank Cells and O Values
I would like to Average the Star Ratings provided by seven people in the Rate Column. The formula I have in the Rate Column is NOT ignoring blank cells or cells with a 0 value. The image below shows the hidden columns that are connected to the formula. =AVG([DH#]@row, [JH#]@row, [RH#]@row, [JM#]@row, [RF#]@row, [KM#]@row,…
-
Seemingly Easy Formula For Countifs on a Rollup Sheet With OR...
I'm working on a rollup sheet where I want to count two instances in the same column. I am currently counting one instance and the formula works fine. When I try to add the second instance it returns unparseable. See below: Current Working Formula: =COUNTIFS({FY21 PEP Dec 20 Cycle (v1) Range 5}, "1", {FY21 PEP Dec 20 Cycle…
-
Need Formula with IF
Hi , I want to create this formula IF the cell's text include specific word or sign than to change it to blank and the same formula condition but to change it to specific text Thank you
-
Commission Formula
Evening, Hope you're all well. I’m hitting a brick wall inserting a formula into a tracker for my sales teams commission structure which is being redone. It’s a tiered system whereby the salesperson will get commission in brackets until they reach target, and then a higher percentage on anything over target; (0.1% for the…
-
Newbie: INDEX and cross-sheet reference.
Hi...and thank you for any help you can offer. It is quite a transition from Excel and Google Sheets to SmartSheet, but I know it's going to be worth it! I am trying to make a master sheet and then use different columns of data in other sheets. I know this works for 1 row: =INDEX({Symbol}, 1) I cannot figure out how to…
-
How to convert a number with a fraction reading as text, to a number (3 2/3) to 3.66
I'm having an issue with incoming data being read as a text. I need it changed to read as a number so I can incorporate formulas. I've tried a few value formulas. The initial formula to pull the numbers from the original text is substitute function. Thanks in advance
-
Symbols Color Change based on Dates
Hello, I'm looking for a formula that will automatically change based on close we are to the due date. So let's say the following: 30 days till the live date, Green 15 days till the live date, Blue 5 days till the live date, Yellow under 5 days, red Thanks in advance.