-
Formulla to generate met, not met, partially met and N/A
Hello, Can any one help? I need a formulla to generate the % "Met", "Not Met", "Partially Met" and "N/A" which is provided in a spreadsheet on a monthly basis. So I will have one spreadsheet with the incoming data. The formulla generates "# Divisible by zero (see attached): I am using the following formulla:…
-
Countifs forumla with 3 criteria's
I'm have used a countifs formula to count the total number of jobs per month that have been launched in my business using the following formula: =COUNTIFS({Project Launch Input 2021+ Range 3}, >=DATE(2022, 6, 1), {Project Launch Input 2021+ Range 3}, <=DATE(2022, 6, 31)) I now what to be able to count the number of jobs…
-
Join Formula from Reference Sheet
I am trying to use Join Formula to concatenate 2 adjacent cells from different columns of another sheet. It returns the value correctly actually, however, as I am trying to drag the formula down, it doesn't work anymore unless I specifically edit again the formula in each cell which is impractical as I have more than a…
-
VLOOKUP CROSS SHEET REFERENCE
MY QUESTION: I am using a vlookup/cross sheet reference to populate when specific criteria are met, such as vendor name and contact number. =IFERROR(IFERROR((VLOOKUP(Payee@row + [Agrmt No. 1]@row, {MATCHING ACTIVE CONTRACTS Range 6}, 6, false)), (VLOOKUP(Payee@row + [Agrmt No. 2]@row, {MATCHING ACTIVE CONTRACTS Range 6},…
-
Need a formula that will not give errors? Multiple "ifs" and a "contain" or "has"
Here is basically the question: IF the sales Order matches and the STR ID# contains or has , and it is marked shipped. Sheet where formula needs to be and the lookups: Cross reference sheet:
-
VLOOKUP with reference to another sheet fails randomly, empty columns (and filter issue)
Hi, I have a sheet with a few cells that uses vlookup to another sheet. At random times, the vlookup doesn't fail per se, it just updates the cell values to empty/blank. If I refresh the page, the values will reappear. But having to refresh the page every time this happens is very annoying. On top of that, I have a filter…
-
Update a Set of Dates when a Specific Date Changes
Hello Smartsheet Community, I currently have a sheet that keeps track of our project dates and tasks, similar to how Microsoft Project works. We have one column with a date value labeled "Field Date" and another Column labeled "Work Date" with a date value as well. The idea is that when we update the Field Date value to…
-
Countif with Multiple arguments for external sheets
I am looking to count the total number of specific persons within a smartsheet using COUNTIF. I have a working function for one person, but I need to integrate multiple persons. This formula references a seperate sheet so my code is a little long. How can I make this work for me? This works =COUNTIF({external sheet name},…
-
sumproduct
Hello I am needing an alternative to a sumproduct funtion. I have two sheets, one with the raw data (ID, Date and Code) and a separate sheet with ID, Code, and a field for total days. The raw data has multiple dates per ID and code, so I thought it would be a Sumproduct/countif combination, but found that sumproduct is not…
-
Is my SUMIFS formatted improperly?
I'm looking to sum $ column when Status column is "Contracted" and when Job Type column is either "Balcony" OR "Roof Anchor" This is what I have: =SUMIFS([$]:[$], Status:Status, "Bidding", [Job Type]:[Job Type], OR(@cell = "Roof Anchor", @cell = "Balcony")) The result is: #INVALID OPERATION I feel I'm probably missing…