-
IF OR Question
I'm trying to calculate a Total Amount column where if the Type field is either Faculty or EMT then the Total Amount equals Total Meals * 100 (each meal costs $100), but otherwise is $0. I keep getting errors with this: =IF(OR(Type@row, "Faculty", Type@row, "EMT", [Total Meals]@row * 100, 0))
-
Count within last 30 days if a specified value
Hi! Could someone offer me some help with my formula below please? I need to count how many rows have a date within the last 30 days in column 'Timestamp', and have a value above 8 in column 'NPS', and have a certain name in column 'Centre': =COUNTIFS({Feedback mastersheet Range 1}, <=TODAY(30), {Feedback mastersheet Range…
-
#INVALID OPERATION Error
I've been getting an #INVALID OPERATION Error for the last several hours. The error was coming up in a different formula in a different column (that was also attempting to multiply a number times the Tax Rate), and as I stripped out criteria, it became apparent that Tax Rate is causing the error. Below is a simplified…
-
COUNTIFS formula troubleshooting - multiple criteria and checkboxes
Hi SmartSheets Community, I'm new to Smartsheet (but know some basic Excel), but am getting stumped on this formula. Can you advise what I'm doing wrong? I'm trying to create a formula that 1) counts either Red or Blue cells in another spreadsheet column (Other Sheet Range 1), and then 2) counts a checkmark (or no check…
-
If current Fiscal Year, check box
I am trying to create a formula that checks a box if the date in the Date Column is this fiscal year - which for us is July - June. Here is what I have so far which is not giving me an error but also not checking the box for the date which is this FY (4/22/22). =IF(AND(MONTH([Date]@row) > 6, YEAR([Date]@row) + 1 = "2022"),…
-
How can I trigger an IF statement only once?
I have a column that is a single select drop down menu. We use this to keep track of who is responsible for that line as the project moves through our departments. A couple of the selections are outside our company. We want to record in a separate column who the outside contractor is. I am attempting to copy the name of…
-
Regarding Index/Match function
I have a work sheet that uses Index/Match formula. The reference sheet is separate from the worksheet. Question - If I share both sheets with a coworker and they save both on their computer. Will the sheet be able to find the reference sheet without any intervention (i.e. redoing the formula) Thanks
-
cell change base on date
Hello, I have a sheet that I'm having trouble getting the right color to apply to the cell. I have 3 colors: green (1-7 days), yellow (8-14days), and red (15+ days). The trigger for 8-14 seems not to be working. See attachment for details.
-
Pulling in data from another sheet
I have a master file in smartsheet that was imported from an excel file. A column of data was deleted and I need to get that data back in the smartsheet file from the main excel file. I would like to: look at column 1 in the smartsheet file and look for a match in column 1 of the excel file if there is a match, then I…
-
Need assistance writing a formula for one column with two purposes
Hello! I am creating a budget sheet and I would like to have a formula that calculates BOTH: 1) SUM across several cells in a row when box is not checked AND 2) SUM down a column if box is checked. So far, I have been able to write a formula that calculates the SUM across several cells (and have added in a bit to return a…
