-
Countifs for cells containing a letter
Hello, I have a formula to count how many hours of work I have assigned to me in a given week. It has the following inputs: "Owner" and "1st day of week" and it looks in a different sheet for all rows that meet that criteria and returns the estimated hours for the tasks. My formula is below and I want a solution for the…
-
Help with an Index Match Formula - If Error Required?
Hello! Currently using the below formula to pull data from one sheet to another. =INDEX({Survey Home Name}, MATCH(Home@row , {Optician Ear & Dental Care Company Survey Range 1}, 0)) I'm getting a #NO MATCH error for any data not yet on the first sheet, but I would preferably like this to return as 'no'. I wasn't sure if I…
-
Subject: Help with INDEX/MATCH Formula – Pulling Data from Sheet A to Sheet B
Hello, I’m having trouble with an INDEX/MATCH formula that's intended to pull data from Sheet A (Zoho) into Sheet B (Hub) based on matching email addresses. Here's the formula I’m currently using in Sheet B: excelCopyEdit=INDEX({Zoho Report Range 2}, MATCH([PERSONAL EMAIL]@row , {Zoho Report Range 3}, 0)) However, the…
-
Calculate Days Open?
Hi everyone, I am struggling with figuring out what formula to use to calculate days open between two date columns on a Sheet. Column 4 = Submitted At Column 5 = Closed Date Column 6 = Days Open No matter what formula I try to use (NetworkDay/NetworkDays/Sub/Days, etc), I get the #invalid Data Type error. Any ideas why?…
-
duplicate entry checking 2 sheets
I have an invoice intake that pulls into an 'In Process Invoices' sheet. Once the invoice gets to a certain approval point it moves to another sheet 'Completed invoices' I need to ensure that there is a notification/color change/something that notes that an invoice number is a duplicate. i.e. once the new invoice is…
-
Formula to pull latest updates from a row
I am trying to setup a dashboard that gets updated with KPI's monthly. But, I only want the most recent entry to be reflected in the charts/dashboard. I get the gist of how to do it with if/or/isblank formulas, but can't quite get it right. It's reversed right now, I can fix that. Am I on the right track here, just need to…
-
How to use a nested if with count
We would like to have all the criteria for a created date by week. the number of new helpdesk tickets created by each end user for a given week. Example: Week of 7/7/25 Mickey submitted 5 tickets, Donald Duck submitted 10 tickets, part of Donalds 10 tickets only 5 were reported in week 7/7/25. Report Week of 7/7: Name:…
-
Rank Formula Multiple Results
I have previously received help with Ranking formula where there is more than 1 result it needs to rank the most recent week, which was resolved and worked fine - until last week. https://community.smartsheet.com/discussion/105462/duplicate-rank-what-formula-would-return-the-most-recent-week The new issue is that I have…
-
Using If And with symbols
Hello, I'm currently trying to write a formula and am running into issues. I've added a "Project Health" column with red, yellow and green symbols. I would like these to change automatically using the following cells to determine. If "Arrival Date" is blank, and "Customer Requested Delivery Date" is within 2 weeks, red If…
-
How do I only get the 25, 26, 27, etc out of 2025, 2026, 2027?
I need to pull out the last 3rd and 4th characters of a long year format CCYY where I only pull out the YY. So, in my example, I need to find the year 25 out of 2025. 26 out of the year 2026, 27 out of the year 2027 and so on. Basically ignore the first two characters of the long year 2025 so I will only be seeing the 25…