-
SUM(AND(ISBLANK formula question
This is my first post on this forum, though I have been using it for years. I appreciate all the brilliant answers that have helped me. I am having an issue with the ISBLANK function. We have a guest list that took on a mind of it's own after guest numbers on different forms were modified depending on which executives were…
-
Is there a formula to show as %
What I am trying to do is have my overview brought in automatically each month so it doesn't have to manually be typed in. The struggle I'm having is it wants to bring in as a decimal instead of a %. Below is the formula I am currently using. Is there a way to make it show as % ="We are at " + [% closed rate]@row + "…
-
Portfolio Metrics not updating after category changes
Hi! I am using the pro template set and I have changed the categories on my project intake sheet but am at a loss as to how to reflect this category change on the portfolio metrics sheet. This leaves me without the appropriate categories reporting. Has anyone solved this problem? How?
-
IF/AND Formula
Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from "unparsable" to "incorrect…
-
countif events from last year
I am trying to count how many event happened last year (2022) as opposed to this year with a countifs, I can't seem to find a countif(ref, year(2022)) or countif(ref,YEAR(@cell)=YEAR[fiscal year]2) with [fiscal year]2=1/1/2022 to be working. Since the countifs is a number with multiple criteria and I'm also looking in a…
-
Invalid data type when using formula
I am trying to create health status column in my sheet and I want it to generate for me a colored ball in the column and I am using this formula: =IF([% Complete]@row, "Blue", IF([Due Date]@row, <TODAY(), "Red", IF([Due Date]@row <= TODAY(5), "Yellow", "Green"))) I am getting the INVALID DATA TYPE message in some rows,…
-
How do I create a IF(AND formula combining multiple cross references?
In this screenshot I am sharing my rollup data sheet. Of the 6 Total Amex CB Count, I am trying to determine how many of those each of our Branches has but I keep getting different errors using formula: =COUNTIFS({Dispute & Chargeback intake sheet branch}, CONTAINS([Data Points]32, @cell), IF(AND({Dispute & Chargeback…
-
Formula to Handle SLA Due Date vs Campaign Start Date
Hi, Smartsheet Community! 👋 This is my first post. Thanks in advance to anyone offering help. Much appreciated! Lots of text ahead but I'm trying to include all relevant details and context. SITUATION We recently launched a new digital marketing request form. On the request form, there are fields for Campaign Type and…
-
Formula to referance availible remaining stock based off used stock
I am trying to build a sheet to track a number a different brace types. How would I build the formula to take the total owned braces by type, subtract total used by type across several rows. This would be the place holder for reference of type and total available owned. This would be the entry as braces are assigned out to…
-
Why is my IF(AND(HAS function only checking part of the AND statement?
I am using a checkbox column to return 1 if a row on this sheet matches the Student Name and Location Name on a row of another sheet. Formula: =IF(AND(HAS({Extern}, [Student Name]@row), HAS({Location_No_ID}, [Assigned Clinic]@row)), 1, 0) The result is showing a checkbox when only part of the statement is true. I need both…
