-
Help for INDEX/COLLECT with multiple criteria
Hi everyone! I usually use INDEX/MATCH, but I am trying to "match" multiple criteria and found that it is better to use INDEX/COLLECT when dealing with multiple criteria. I keep returning "#INVALID VALUE" and am confused what I am missing… I have an "Audience" sheet my team is using with employees' respective name, ID…
-
COUNTIFS
Hi, I have this formula below but its picking up everything that contains small fixes. We have small fixes, small fixes delivered and small fixes closed but I would only like to find results that are small fixes only. Can you help? Here is the formula I have =COUNTIFS({TL}, FIND(Metric@row , @cell ) > 0, {IS},…
-
Sumifs formula application questions
I'd like to apply for sumifs formula in sheet, but seems the function cannot capture the correct sum amount, details as following: Expectation：sum up total service fee from legal entity: 137 with quarter: Q1, and I list the sumifs function as below： The result from the above formula comes up as 55527.49 while the correct…
-
COUNTSIFS does not Contain
Hello, I've put together the following formula, to count certain items in a different sheet that don't meet certain criteria, please see below. =COUNTIFS({SE-Region}, Region@row, {Status}, <>"Completed", {Status}, <>"Canceled", {Status}, <>"Duplicate", {Summary}, <>CONTAINS("Onboarding", {Summary})) The formula works until…
-
Need help with an Index/Match using TEXT for Dates
Can someone help me understand how I can correct this formula? It's returning Unparsable. =INDEX({Revenue & Opex | ARRONI Row}, MATCH(TEXT(DATE(YEAR({Revenue & Opex Go Live Date}), MONTH({Revenue & Opex Go Live Date}) + 11, 1), "MM/DD/YYYY"), {Revenue & Opex Date Header}, 0)) The formula is indexing a full row. Matching a…
-
How do I make a form field's visibility dependent on 2 fields?
Hi, I've been struggling with this with my favourite AI for over an hour now and I've hit a wall. I want to implement the following: if [CheckboxA] show field 1 if [CheckboxB] show field 2 if [CheckboxA and CheckboxB] show field 3 There is no way to do this within SmartSheet forms. You have to have a helper column in the…
-
How to count if checkbox is checked
Hi, I need to count the total number of items that are in progress that are green but I only need it to count the ones where that criteria is met and a checkbox is not checked. I know the formula below works for counting just those in progress and green but I'm struggling to add the checkbox portion to it.…
-
Max Collect Formula for Dates in 1 row?
I have read other help articles, tried writing with AI and still could use some help in writing a formula for the following scenario. I have three date columns. and want to return the value of the date that is the next date but in the future. if the dates were: 10/1/25, 10/10/25 and 11/1/25 it would return 11/1/25. This…
-
List of U.S. Federal Holidays in MM/DD/YY Format
I need to add business days to a date, and the guidance I'm finding on this seems very arduous and labor-intensive (and it's taking ChatGPT 10 minutes to come up with a list in the proper format!). What are you all doing to manage U.S. Federal Holidays in your Sheets for calculating business days? There has to be a quicker…
-
INDEX COLLECT MIN (again)
Had a follow up question to my previous question located here. — I was using the original sheets, which I fixed with the solution from that thread, as a template. Today, I copied them over to essentially create the same system for a different LOB. I checked the box that updated all formulas to the new references. Now, in…