Best Of
Re: How to Create a weekly report and make it automatically go out to stakeholders at a specified time e
Kowal,
Thank you for the information. It's just what I needed.
Respectfully,
Augie
Augie
Re: IF COUNTIF Formula help
Getting the formula to only check a single box for each school involves a helper column. Auto-number (called "Auto" in this example) with no special formatting. Then the formula for the checkbox changes to:
=IF(COUNTIFS([High School]:[High School], @cell = [High School]@row, Auto:Auto, @cell <= Auto@row) = 1, 1)
Or in a sheet summary field or a separate sheet (updated with cross sheet references of course):
=COUNT(DISTINCT(COLLECT([High School]:[High School], [High School]:[High School], @cell <> "")))
Paul Newcome
Re: Community Corner Newsletter [May 2025]
Hi Max,
Congratulations on your qualification! I'm happy for Spring too. Winter stuck around way too long this year. Thanks for another great update! Lots to peruse and explore.
Ray Lindstrom
Re: Community Corner Newsletter [May 2025]
I'm such a fan of you, Max! Congratulations on achieving this new milestone. It is truly inspiring.
So many product updates, yay! 💙
Also, I relate so much with @Carson Penticuff, this Community is one of the biggest flex Smartsheet offers, and this is possible thanks to each member.
Marcela
Re: IF COUNTIF Formula help
Hello @Stacey Carrasco!
Just wanted to @mention you to ensure you don't miss Paul's response.
Cheers
Marce
Marcela
Re: Help with writing a formula to show Completed and Past Due along with other status.
I see another typo. I didn't close the AND agreement. Sorry about that:
=IF([% Complete]@row = 1, "Complete", IF(ISBLANK([% Complete]@row), "Not Started", IF(AND([End Date]@row < TODAY(), [% Complete]@row < 1), "Past Due", IF([% Complete]@row < 1, "In Progress"
Nic Larsen
Re: Help with writing a formula to show Completed and Past Due along with other status.
PERFECT!! it works, thank you!!
Elizabeth Taft
Re: Question on status rollup using IF COUNTIF formula
The initial issue is that the comma after "Complete" at the end needs removed.
You could also have it count the same as "Complete" by using an OR function like so:
=IF(COUNTIF(CHILDREN(), OR(@cell = "Complete", @cell = "N/A")) = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress"))
Paul Newcome
Re: Formula help to convert from total days to business days
How about this:=IF(AND(NOT(ISBLANK([Construction in Progress Date]@row)),
NOT(ISBLANK([Into Pending Lateral Date]@row))),NETWORKDAYS([Construction in Progress Date]@row, [Into Pending Lateral Date]@row),"")
It ensures both start and end dates are present.
Only then computes business days, otherwise, returns a blank (""), not 1 or error
=Chris Palmer
Re: FORMULATE FOR DAYS
@KempenUSA you want an if then where your if check is whether dept date 2 is blank.
=if(isblank([DEPT DATE-2]@row),NETWORKDAYS([DEPT DATE-1]@row, [RETURN DATE TOTAL TRAVEL DAYS]@row), NETWORKDAYS([DEPT DATE-1]@row, [DEPT DATE-2]@row))
prime_nathaniel