-
IFERROR + IF/ISBLANK
This formula is almost perfectly pulling downtime by examiner and production date: =IFERROR(INDEX(COLLECT({downtime - downtime}, {downtime - production date}, [Production Date]@row, {downtime - examiner}, Examiner@row), 1), 0) There are a few records that are not in error, but the result is a blank, however, I need those…
-
Where do I put the IFERROR function to correct for year?
I have a column of dates. New rows are being added over time. I count how many occur in each month. In a separate metric sheet, have 12 formulas, 1 for each month, that look like this. =COUNTIFS({Date Column}, IFERROR(MONTH(@cell), 0) = 1, {Date Column}, MONTH(@cell) = 1) for January =COUNTIFS({Date Column},…
-
Consolidate Multiple Column Cells Into 1 Based on Cell Value in Row
I am working on a telecom cleanup project and have an export of all the telephone numbers at each site. I'm wanting to take the telephone numbers in each cell and combine them into 1 based on the branch number. The closest thing I could figure out was to run a collect function based on the branch number but am getting an…
-
Sum if status is approved and end date is in the future
I am trying to get the sum of approved projected expenses only. I can't figure it out I have been trying SUMIFS and SUM(AND formulas but can never get it to return a result. I can get one or the other to work, but it's important that only approved future expenses are summed. Below is a screenshot of the columns I'm working…
-
COUNT(COLLECT) Status and Dates
Hi Super Guru's, Could I kindly ask assistnace on where my formula is incorrect. Getting #UNPARSABLE error =COUNT(COLLECT(Status:Status, ="Complete"),[Finish Date]:[Finish Date], >=DATE(2024, 1, 1), [Finish Date]:[Finish Date], <=DATE(2024, 12, 31)))
-
Formula incorrectly stating two cells are not equal
I have two columns, one is an index collect formula and the other is a simple subtraction of two cells. I want to create a formula to display a message if the two cells are not equal. I created the following formula '=IF([UOB QA]1 <> UOB1, "Check UOB")' and as you can see below, the two columns are identical and it's still…
-
Automated Drop down or Formula?
Hi all, I was wondering if there is a formula or drop down function that can auto populate words or drop downs? As seen in my conditional formatting for "Days Left Till Expiration it conveys whether if it is red, Yellow, Or Green based on the amount of days passed or upcoming till a Term end date. I'm wondering if I can…
-
I'm trying to calculate the number of instances where only two items appear in a cell.
I'm trying to calculate the number of instances where only two items (the words content and functionality) appear in a cell. My current formula is not pulling the correct count. Any suggestions? P.S. I'm a formula rookie :) =COUNTIFS({Sub Type Update}, "Functionality", AND "Content", {PhaseForecast}, OR(CONTAINS("Planned",…
-
Searching within collected values from across multiple sheets
I am having trouble coming up with a way to search within values using a Collect formula. I have three sheets, one which shows the courses a user has completed, one which shows the courses included in each learning plan {CourseMapping_Course} {CourseMapping_Paths}, and one which shows the learning paths assigned to each…
-
Avgifs between dates
Hi SmartSheet Gurus, Not sure where I am going wrong here. I keep getting an unparsable error message
