-
Unable to SUM column totals that include "#No Match" cells?
Please help me understand why my formula isn’t working. I am trying to sum and average the total of a column containing serval #NOMATCH cells. I have tried two different formulas that return the following results. =IFERROR(SUM([Year 1 Cost]:[Year 1 Cost]), "") Returns blank cell =IFERROR(SUM(AVG([Year 1 Cost]:[Year 1…
-
VLOOKUP/JOIN/COLLECT - Need help combining groups of emails
Good afternoon, I am stumped & hoping someone can help. In short, I have a Reference Sheet with the contact info and a Communication Plan Sheet. In the Reference Sheet, I added an Address/Filter/Email List column. The Email List is grouping all of the addresses based on which group they are in, so when you copy the Email…
-
Return a value based on two criteria.
Hello Smartsheet Community! First time posting a questions so here goes.... I need some assistance and am stuck with a formula. I have a Master File that looks at a lookup file for data. I have Customer ID that have a Tier I rate and a Tier II rate based on a location. I already have in my file what Tier is expected but am…
-
Report Grouping based on Multi-Select Drop Down, with the same "data" listed under multiple groups
I have a list of technicians who support calls from different phone queues. I need a report that will group all technicians who support each one. And as in any call center, technicians never support a single queue and will therefore need to show up under each queue group. This is what my sheet would contain: And this is…
-
RYG health and status indicator roll-up
I currently have something similar like this setup for a number of projects, for each of the deliverables, I use the following formula: =IF(Status4 = "Complete", "Blue", IF(Status4 = "At Risk", "Yellow", IF(Status4 = "On Hold", "Yellow", IF(ISBLANK(Finish4), "", IF(Finish4 - TODAY() < 0, "Red", IF(Finish4 - TODAY() < 3,…
-
Need help ASAP on how to perform output perimeters in Smartsheets
@Genevieve P. i need help on this ASAP. please explain step by step on how I would configure this in the Smartsheets form webpage How would you configure this in Smartsheets when you enter the OTB number the output form will populate only the information on the specific row of that particular OTB number entered in based on…
-
Alphanumeric number parse to just the alpha
I have a column with an alphanumeric number "CYG2525" or it could be "C2525", "GY2525". As you can see the alpha can range in the amount. I need to be able to pull over just the letters so "CYG" or "C" or "GY" for example. I thought I could do this with saying give me everything left of the "2" but the issue would be when…
-
Require Help with COUNTIF Formula on Smartsheets
Hello everyone! I am trying to count the number of records for the current year(2021) from 'System Generated' date column -(Request Date )with formula - =COUNTIF(Request date: Request date, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())) in the 'Sheet Summary' section. This is not working - says #UNPARSEABLE Alternatively I have…
-
Countifs + Contains/Find + Multiple Criteria
Hi, I have a column that is a multi-select with contact options and I'm trying to create a formula that Counts the amount of times a person's name shows up in that column AND also matches the entity name selected in another column (single select). I have two separate formulas but I can't seem to combine them the right…
-
Multiple COUNTIFS plus OR
Hi gurus! Here is our question of the week: I have a formula with multiple COUNTIFS, but I also need an OR...with a COUNTIF and CONTAINS. But this doesn't seem to be working in my favor! (Getting the Unparseable error.) Here's what I have so far: =(COUNTIFS({Sheet1}, "2020", {Sheet1}, "3") + COUNTIFS({Sheet2}, "2020",…
