-
Using OR for different ranges
Hello, I have a formula I'm trying to improve: =COUNT(DISTINCT(COLLECT({Location}, {District}, [Primary Column]@row, {Order}, 1, {Health}, "red", {Category}, OR(@cell = "Category 1", @cell = "Category 2"), {End Date}, >=DATE(2025, 8, 15)))) + COUNT(DISTINCT(COLLECT({Location}, {District}, [Primary Column]@row, {Order}, 1,…
-
Cross Reference Sheet Formula Help
I am needing help on a formula. I am trying to pull a clinical placement status from one sheet over to another but only if the hospital name and unit department matches. I also need to match the date to the cross reference sheet over 16+ columns that could have that date match. I am currently using =IF(HAS({Day of week…
-
IF(MATCH with Multiple Criteria Across Sheets
I am attempting to create a formula that will search another sheet for a product code and as long as the product code isn't listed as "Canceled" on the other sheet, return "Yes". I started with =IF(CONTAINS([Product Code]@row, {Upcoming Products}), "YES", "NO"). When I tried to add the condition, I got a little lost on how…
-
How to create either a conditional formatting and/or use IF(AND) Formula for Attendance Tracker?
So I'm trying to create a formula or create conditional formatting for attendance. Every 1 Unexcused Absence within 45 days is 1 Occurrence that must be documented with the employee. Every 2 late arrivals/ tardies within 30 days count as 1 Occurrence that must be documented with the employee. So if my employee was…
-
Counting occurrences of a string in a row
I am trying to count the number of times "N/A" is included in a single row. I know it can been done with a single range with the below equation, but in this sheet there are some columns that want to exclude. Is it possible to get a count of the "N/A"s in the sheet with multiple ranges to exclude some rows? =COUNTIF([Column…
-
Help with a COUNTIFS Formula that contains OR and Divide to calculate a %
Hey Guys I wonder could i pick your brains to iron out the kinks in my formula I would like to calculate a % based upon the following references Sheet Reference {EPIC} Value "GBT-xxxx" Sheet reference {JIRA STATUS} Value "Pass" or "Fail/Issue" or "Gap" or "Pass - Now Fail" or "N/A (Not Applicable)" Divided by 234 which is…
-
Struggling with an IF/OR/AND formula
Hi, I have a formula I'm struggling to put into a single IF/OR/AND formula! Basically, if the [Add-ons] column includes the tag "Prebill" AND if the [Final Invoicing] column is “Invoiced”, I want the [Total Amount Invoiced] column to add [Prebill Actual Invoiced] and [Final Invoicing Amount]. To clarify, if just one of…
-
Formula not pulling data
I have imported Clockify timesheet data from a csv file into Smartsheet. I am trying to pull number of hours spent on a project and project task per month. When I enter the formula, no data is returned - not even an error message. What am I doing wrong? Formula I'm using: =SUMIFS({TEST - Timesheet duration decimal}, {TEST…
-
Formula for updating window of time to different time zones...
I am creating a form to schedule meetings however, I need the times I have listed in Pacific Daylight Time (PDT) to be converted into Mountain Daylight Time (MDT) (PDT+1 hour), Central Daylight Time (CDT) (PDT +2 hours), Eastern Daylight Time (EDT) (PDT +3 hours), and Greenwich Mean Time (GMT) (PDT +7 hours). I am manually…
-
COUNTIF and HAS calculations
I am trying to get metrics where if 'NDC' is found on another sheet, and it has Under Review, Pending Development or Pending Production Release, then it will calculate the total of NDC items that i have. I tried this command, but it's not working and i dont know how else to achieve it. =COUNTIF({Sabre Traveler Care…