-
Count of Multi Select Options Across Entire Sheet
I can find some reference to this that others have asked but it seems slightly different then what I'm looking at. Consider the following: Column: Type of Issue A, B, C A, B A, D A, C, D C, D I'm looking to place a count of each of the items above on a dashboard across the entire sheet. Any insight would be appreciated John
-
Issues with CONTAINS using Cross Sheet Reference
Hello, I've created a COUNTIFS formula referencing another sheet. Each section of the COUNTIFS references the same sheet so that isn't the issue I believe. Everything works up until the CONTAINS portion. Is there an error with my syntax? =COUNTIFS({Status}, New1, {GSP Category}, "New GSP Onboarding", CONTAINS("Onboarding",…
-
Leave blank if SUM is zero
I found a few answer that I though would work for us, but I keep getting an Incorrect Argument error. I want to sum numbers in columns for a total, but leave the total blank if the sum is zero. Here is what I was trying: =IF(COUNT([MD FTE]:[MD FTE] > 0, IF([APP FTE]:[APP FTE] > 0, SUM([MD FTE]:[MD FTE], [APP FTE]:[APP…
-
Pulling Multiple Rows of Data into 1 Cell
Hello, Does anyone know how to pull multiple rows of data into one cell? I'd like to build a cell that look-up the date "05/20/20" and brings all the values into one cell. I'm thinking vlookup: =VLOOKUP("5/20/20", {Test Range 1}, 2, false) + " - " + VLOOKUP("5/20/20", {Test Range 1}, 3, false) + " - " + VLOOKUP("5/20/20",…
-
Avoiding #INVALID DATA TYPE when cells are blank
Hello, I am setting up a staffing leave Smartsheet for my team and unfortunately I am getting the #INVALID DATA TYPE error message. There are 2 formulae set up within the sheet: Leave Remaining (the dark grey row) is calculated as follows: =[Leave Allowance] - Taken + [Leave Bought] + Carried + [Long Service] Taken (the…
-
Auto check box using =IF(Primary@row="California",1,0)
This formula works as typed. However, I need your help writing this formula such that it will auto check box if the word is "California", or "Florida", or "Arizona".
-
Set RAG based on % Complete & Deadline Date Proximity
Hi I am trying to get my formula to set a RAG status based on the % complete and deadline date proximity. My formula is: However, the last entry with a deadline date of 12/11 show be showing a "Yellow" RAG but, it still shows "Red". What have I missed? TIA Cheryl
-
Proper Syntax for IF and VLOOKUP
I am trying to create a formula that will run VLOOKUP if a cell is blank This doesn't error but it leaves the formula field blank =IF((USERR3 <> ""), (IF((VLOOKUP(SDR3, {SFDCUsers Range 1}, 2, false)) = 0, "No", "")))
-
Reference Another Sheet without Function
We use "Reference Another Sheet" quite a bit in our sheets. We do this by starting a function in a cell, then putting an open parenthesis, then clicking on the link in the resulting dialog box that reads "Reference Another Sheet" . This works fine. The thing I'm running into is sometimes I want to reference another sheet…
-
COUNTIF with OR statement
Hi! I'm trying to count the number of cells from two different check box columns (cross-sheet reference) which show a check in either one of the columns. I'm getting an UNPARSEABLE error. Could you help me out with this? Thanks!