-
AverageIF- Date range and multiple criteria from SS source
Hi, I keep getting unparceable, please help! Need the average of all days in source 1that meet this criteria: source 2 ="closed", and source 3 has a date range of 1/1/21-3/31/21. Source 1-{Tracker Range 8} Source 2- {Tracker Range 4} Source 3- {Tracker Range 3}
-
Is there a formula to specify "Completed" if children "Completed" or "Nothing to Submit"?
The formula I'm using currently returns the cell as blank if children are "completed" or "nothing to submit", but I would like it to return "Completed" in that case. =IF(COUNTIF(CHILDREN(), "Complete") + COUNTIF(CHILDREN(), "Nothing to Submit") = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Not Started") =…
-
Multiple VLOOKUPS in one formula?
Is it possible to create a formula that has multiple vlookups from different references? If so, how? I've tried, unsuccessfully. My goal is to create a formula that tracks the most recent number in a cell aug -10, sept-20, oct - "blank" so the cell will show 20. We have 4 sheets that track this info on a quarterly basis…
-
Auto Renew every X years
Hi, I'm trying to calculate the dates that contracts will auto renew. One column has the effective date, the next column shows how many years it will auto renew for, and I want the expiration column to show when the auto renew is up. So if the contract is effective 9/1/22 and is auto renewable for two years, the expiration…
-
COUNTIFS(OR with multiple checkboxes
Hi, I have a formula where I am trying to say in column 1 count if checkbox A is checked OR in column 2 if checkbox B is checked. Here is the entire formula with the portion I cannot figure out in bold: =COUNTIFS(Simulation:Simulation, "Phillips ED", Complete:Complete, "0", Type:Type, OR(@cell = "Pilot", @cell =…
-
Smartsheet doesn't view known smartsheets im looking to reference?
Creating a reference sheet from one Smartsheet to another (looking to create a dashboard in future). However I am trying to vlookup or countif but it doesnt show a sheet that I full admin access. Trying to execute a simple step but cant find these sheets, iI have previously performed these tasks with other sheets. Thank…
-
Trying to combine IF, OR, & AND
Hi all, As the title states, I am trying to create a helper column that is checked or unchecked based on a few conditions. I want to check the helper column box if statements 1 and 2 are both true or if statements 1 and 2 are both false. I had written: IF(OR(AND(statement 1@row = 1, statement 2@row = 1), AND(statement…
-
Shortcut for nested IF function
I am tying columns together in a sheet so that when the user selects a product from a dropdown from a cell, it will automatically populate the product's manufacturer. I can do this using the following: =IF([Product Description]@row = "Product 1", "Company 1", IF([Product Description]@row = "Product 2", "Company 2",…
-
Sheet Summary formula to pull value of cell based on latest date
Hello Community - I've scoured the forum and can't find a solution for my particular situation. I have a sheet where I want a summary field to pull in the value in the CL Readmit VAR column based on the most recent date in the Modified column. The sheet will continue to populate rows from a form so I need the summary field…
-
Countifs when there are different answer options
Hello, I want to collect data on the number of times certain machines are reported between certain dates Here's is the basic formula that works. =COUNTIFS({NS Repair Detail Report Type}, "OOB", {NS Repair Detail Machine Model and Description}, "Machine Name 1", {NS Repair Detail Activity Date}, >={Sept 1 2022}, {NS Repair…
