-
Countifs with a range based on greater than and less than
I'm having an issue writing a formula that I thought would be pretty easy. Essentially, I have a Sheet Summary of Tasks In Progress that I want to count all rows that have "Task" checked and "Progress" is less than 100% and more than 0%. My formula for Tasks Not Started works by counting only the 0% and my formula for…
-
Time Duration Calculation
I've seen a few ideas on similar topic threads but none of them worked quite right, so I'm trying again! I have a form staff fill out to record their overtime and the reason for it. Does anyone have any formulas that I can use to populate a column showing the numbers of hours:minutes of overtime based on the "Scheduled Out…
-
Using Multiple Checkboxes to determine and auto-update % Complete
I have some tasks for an implementation Smartsheet that require different steps. I would like the % Complete column to auto update to 33%, 67% or 100% based on the boxes checked. Obviously if the final box is check it would be the 100% and ignore the 2 boxes that came before it. I attached a screen shot with the formula I…
-
SUMIFS with multiple OR criteria using CHILDREN?
Just curious. Looking for a way to speed up this scenario: =SUMIFS([Range to sum]:[Range to sum], [Criteria range]:[Criteria range], OR(@cell = [Criteria]1, @cell = [Criteria]2, @cell = [Criteria]3)) Is there a way to transpose that OR statement into something using CHILDREN so that I can plop a list of a large number of…
-
COLLECT date values from another sheet
There are 2 sheets with the same set of columns: Code / Crop / Start / End / Cost Start and End Columns are dates. 1) I need to collect date from column Start which satisfies the conditions Code = 1122, Crop = Apple. The formula is the following: =COLLECT({Sheet1 Range 1}, {Sheet1 Range 2}, @cell = Code@row, {Sheet1 Range…
-
Suggestion — Add a formula to retrieve information about the comments in a row
It would occasionally be useful to be able to access information about comments within a formula. For instance: IF(CountRowComments() gt 0, "Has comments!", "No comments") Or even, IF(GetComment(1).Contains('error'), TBD, TBD) (Don't take the syntax too literally, but hopefully you get the idea.)
-
Combining CountIfs statement with Date
I have a CountIfs formula that I'm having trouble getting to work when I add a date condition into it. Basically I'm trying to get a MTD output based on several countifs as well as only counting if an adjecent cell does not contain "Appointment Allowcated". The formula works perfectly when I don't have the IFERROR…
-
Formula based on timelines (start and end dates)
I'm trying to basically show sr. mgnt how many high project projects are all happening at the same time -- ideally I'd like to count them -- is there an easy way to do that? Essentially I'm using project type = High then want to show those start and end dates that fall w/in maybe a quarter range. Is that possible? TIA!
-
Using COUNTIF children to change a Status with multi criteria
Hello! In our Timing and Action we have a formula that indicates the status of a Parent row based off the cumulative Status choices (Not Started, In Progress, Complete, N/A) of the Children Rows below it. How do I get the formula to register the Parent Row 'Complete" when the CHILDREN Rows = both Complete and N/A I want to…
-
Always pull in the value from the same cell
I'm trying to pull in a text value form a 2nd sheet. The value I'm trying to pull will always be in cell [TextToPullIn]1 for example. That second sheet is populated by a form with the latest entry always on top, and the text I want to pull in my first sheet will therefore always be in the same cell. Any ideas? Maybe I'm…