-
IF/OR/AND not progressing
Hi, i am working on an IF statement and am going round in circles. my statement seems to get as far as 3 star in the statement and no further even though the criteria says it should. For 5 star and 4 star it references a different column to count if the ticket number is above 50. Do I need an esle??? =IF(AND(Threshold@row…
-
Large Data Set Solution
Hello, I have created a table to view weekly data displaying how many cases are worked vs hours worked. This table is using several SUMIFS fuctions to pull from 3 separate source sheets. My issue is these source sheets are updated every 2 weeks with roughly 2,500 rows each. So far, I have only done weeks 3-6, skipping the…
-
Count Formula
Hello I am looking for assistance with a formula as I need to count across multiple sheets. I am trying to capture the number of times a word appears. I selected the columns in which the word appears on both sheets and still not able to calculate the total. The formula I am trying to use below gives me an #Inparseable…
-
JOIN COLLECT DISTINCT CHILDREN FROM ANOTHER COLUMN WHERE CHILDREN OF CURRENT IS TICKED
I have two columns. primary and column B On the parent row for column B, I want to look at any children which are ticked and then join collect distinct values from primary column. If I was to do a basic formula in column B it would be this for children: =JOIN(DISTINCT(COLLECT(CHILDREN(), CHILDREN(), <>"")), CHAR(10)) I…
-
Interest Calculation based on multiple column
I am attempting to calculate interest based on a past due value in one of 4 columns. I need this formula to calculate all 4 columns because the amount may appear in one column this week and move to another the following week. I am able to get it to calculate using one column but when i attempt to add more columns i get an…
-
Need help on Countif with OR & AND in report filter
I have following screenshot's report, but I couldn't figure out in formula using Countif formula, I tried with below formula: =COUNTIFS({ProjectSurveyContact1}, OR(HAS(@cell, "")), {ProjectSurveyContact2}, OR(HAS(@cell, "")), {ProjectSurveyContact3}, OR(HAS(@cell, "")), {ProjectStatus}, OR(@cell = "Closed", @cell =…
-
Incorrect WeekNumber or(??) Year when trying to return a 'Week Number of Year' calculation
I am trying to return "Week Number of Year" (I want two digits for the Week Number hence the workaround in the formula) and all my weeks start on a Monday. I have used the following formula which works great until you get to Monday 30 Dec 2024 where it returns "01 of 2024" - clearly wrong as Monday 01 Jan 2024 should be…
-
Can I put a max on a sign-up time slot on a form?
I am creating a sign-up sheet and am providing 3 signup times for people to choose from. I need each sign-up time to have a max of 15 people who can choose this time, is this possible? If so, how?
-
Formula to subtract one month from a date
Hello. I need to subtract one month from a date. I have tried =Date-30, and then minus 60, etc. By month minus 5, I was quite a bit off from the first of the month. If the Date being evaluated is say 4/1/20, I'd like the first calculation to produce 3/1/20, and the next row to produce 2/1/20, and so on. I did find a…
-
Due Date Formula
Someone recently plugged a formula, =MIN(CHILDREN), into the due date column of the sheet I maintain, which is causing the parent rows to have an incorrect, past-due due date. I deleted the formula and replaced it with the correct due date, but it seems the sheet automatically reverts back to the formula due dates. The…