-
NEW! Your survival guide to formulas is here. Meet the Formula Handbook.
Hi Community, Whether you’re summing child rows, calculating budget minus actual, or doing cross-sheet data lookups with multiple criteria, formulas are a must for tailoring solutions to your needs. The new Formula Handbook is here to help formula beginners and pros alike. This template contains: A glossary of all…
-
Join formula started adding a leading zero on text from certain columns
I created this formula, and it worked great the first two times it was used. The third time, it added a leading 0, which is NOT present in the [Circulation Requirement Details 3] column. I did further testing triggering the function using [Circulation Requirement Details 4] and [Circulation Requirement Details 5], and 0 is…
-
I am trying to get the percentile of responses that return "1-2" if the responded "4 Months" in
I am trying to get the percentile of responses that return "1-2" in a column if the response is "4 Months" in another column. =PERCENTILE(COLLECT({Preceptors}, {Preceptors}, ="1-2", {Survey Time}, "4 Months", 0.5)) or =PERCENTILE(COLLECT({Preceptors}, {Preceptors}, ="1-2", {Survey Time}, {Survey Time}, "4 Months", 0.5))…
-
COUNTIFS with multiple OR dates
Im trying to use a formula that counts the number of projects where it fulfills certain criteria that then checks whether the startdate was after a beginning date range or the end date was before the end date range. (basically that work was performed within the date range). This is what I have: =COUNTIFS({Marcom Portfolio…
-
Formula to display one date in a range with 3 criteria
Hello everyone, Can you please help me with a formula? I have three columns: 'Start date' , 'Dose', 'Site'. I would like to consider multiple criteria: 1) the start date is in the future 2) the value in the 'Dose' column is 'drug' and 3) the Site is '01'. Based on these criteria there should be a list of dates, but I would…
-
How to divide single request with multiple contacts to individual requests.
Does anyone know how to take a single line, with a single multi-select cell, and have that line parse/delineate/divide in to individual lines, either through a formula or an automation? Scenario: we are receiving schedule block request for team, and multiple people may be on the block request, therefore we have multiple…
-
=SUM Formula that returns a Zero if cells are Zero
I'm trying to figure out how to get this formula to return a zero if the Age of Closed cases and the count of closed cases is both "0". =IFERROR(SUM([Age of Closed Cases]@row, [Age of Active Cases]@row) / SUM([Count of Closed]@row, [Count of Active]@row), 0) Both are derived from a =COUNTIF that uses a cross sheet…
-
Divide By Zero Error with Average
Hi, I'm trying to set up a formula that averages the scores for four columns, but doesn't bring up the Divide by Zero error if the scores haven't been entered yet (either leaves blank or puts a 0). The Average formula works fine but I can't get the IFERROR part to work. Current formula: =ROUND(AVG([LOR 1]1:[LOR 4]1), 1)…
-
Unparseable for multiple IF statements
Hi, I'm trying to use multiple IF formulas within one cell by transferring from the Excel sheet I created it in, but I keep receiving the "#UNPARSEABLE" error code. Here is the formula I've attempted in SS: =IF([Support Intensity]@row="high", 9),IF([Support Intensity]@row="medium", 6),IF([Support Intensity]@row="low", 3)
-
COUNTIF where counting if cell contains "Super" but "Superstar"
I have a formula where I'm counting how many products are at a certain status and where they have a title of Super, Super & more or Super & Design (B&W) - but want to make it scalable. Is there a way to create a formula where I can tell it to count any cell where "Super" is the prefix? And secondarily, not count anything…
-
Unsure of formula or function to pull sales agent data from 2nd sheet
Currently we input all weekly sales results for our agents into sheet ONE named "2024 Agent Sales Activity" (1 row per agent with sales that week, with the primary column being the agent name "Last, First", and one of the other column is "Weekly Total"). In a SECOND sheet I'm setting up a lifetime sales tracker, with one…