It looks like nothing was found at this location. Maybe try one of the links below or a search?

I read that Smartsheet can't search for specific text and someone on another thread suggested some solution using Find. I'd like to make a sum total at the bottom of a sheet in which I add only amounts if the cell in the same row has any text. See attached and how rows 2 and 3 are the same independent values but together as a string only one is matching. The column that has the district information is a multi-select value hence why the contains. Thank you for contributing to the Community. 2022. My suggestion at that point would be to change the fields to multi-select dropdowns and then replace the CONTAINS function with HAS. It wasn't throwing an error just not returning the expected result. Example: Sum of column Amount, if Status column contains "Completed, if Quarter column contains "Q1, if Budget column contains "Fixed". =SUMIF(Month:Month, or(CONTAINS("January", @cell),contains("February", @cell),contains("March" @cell)), [Number of Visitors]:[Number of Visitors]). I tried typing it in since I already have a joined column. Hi what's the easiest way to add IF contains? All Rights Reserved Smartsheet Inc. , but it only works if there are numbers. Nope that doesn't work I get #INVAID REF. AND that worked like a charm. Within the cell which is our Criteria Range @row What if that cell also had multiple results? Hopefully you get the idea and can make this work. Please can someone help.

Sums the cell values in the Units Soldcolumn if the adjacent cell in the Clothing Itemcolumn of the same row contains the value T-Shirt. =SUMIFS(Amount:Amount, Status:Status, "Completed", Budget:Budget, "Fixed", Quarter:Quarter, "Q1"). The SUBSTITUTE function is case sensitive. The formula above is looking at the length (number of characters) of the entire string, subtracts the non "D" characters, which means what is left should be a string length in multiples of the word "D". I think it's the syntax around the cell range that's confusing me. I see your help frequently on here and I knew I'd find you eventually. All Rights Reserved Smartsheet Inc. :). I've started with something like: =IF(CONTAINS("D", {Values Range 1}), "1", "0"). Another option would be to add a helper column to your source sheet where you join the text of all 4 of those columns together. Hooray! It will require a Text/Number Helper column. The HAS function was specifically designed for multi-select lists to see if the cell HAS a certain value. @Speigel If you convert the yearday column to a multi-select dropdown, you could incorporate the HAS function. @[emailprotected] Thanks for filling in. 2022. This works for the most part as it does count the value occurrences within a string. Try changing the Criteria column over to a mutli-select as well. two of my criteria columns had special character/space, so I had to use square brackets. 2022. I was able to do it with just January, but not the other two months. I'm probably at a loss with this part. But you answered my first part perfectly.

On this row it's possible to have a blank or text (N. Since I always use the plural version of a function in case I later choose to add more criteria, I'm writing it as a SUMIFs. @Genevieve P. It worked!!

I don't want to take advantage of your help here- but if I'd really like for it to work bothwithtext andwitha number. If your column name is a single word you don't need to wrap it in brackets. I also didn't now about the @row and @ cell features so look forward to using that in different scenarios too. Also, is it possible to include multiple criteria from multiple columns? Using the example above: HAS(@cell, "1st") works, but HAS(@cell, [1]2) does not. Perhaps this will help, as I am actually looking for words, not a single value. Note the above is just the first search criteria for the SUMIFS formula.. the other parts of the formula work.. 2022.

See below. Thanks again. Not sure if you saw my comment above about Char(34) that fixed it prior to your last comment or I could have gone down that path too. Hi - I'm struggling to get something similar to work so looking for advice. So update: I tried to add quotes to the criteria range so the cell would read "360" rather than 360. No matter what I changed in the data it only results in the first value of the first cell referenced in the formula. Glad it worked.

2022.

All Rights Reserved Smartsheet Inc. Your formula worked phew.. thank you! Thank you so much, and I hope you get some rest this evening . Can someone help me figure out how to sum a ROW where some of the cells have a number in them and some don't. =SUMIFS({Master Absorption Form Range 3}, {Master Absorption Form Range 6}, CONTAINS([emailprotected], {Master Absorption Form Range 6})). In other words, instead of just counting the values in a single string, i'd like to count them in a range of string values. @Genevieve P. I'm doing something very similar and it works when I hard code the value in the HAS statement. I tried HAS earlier as well with the formula below and it pulls back "0" when it should pull back "2. I tried to use CONTAINS and came up with this, but its not working: =COUNTIFS(Department:Department, "Marketing", (CONTAINS("Marketing")) Start:Start, IFERROR(MONTH(@cell), 0) = MONTH(TODAY())), =COUNTIFS(Department:Department, CONTAINS("Marketing",@cell) Start:Start, IFERROR(MONTH(@cell), 0) = MONTH(TODAY())). =SUMIFS(Status:Status, "Completed", [Amount]:[Amount], (Budget:Budget, "Fixed", [Amount]:[Amount]), (Quarter:Quarter, "Q1", [Amount]:[Amount])), =SUMIFS([Amount]:[Amount], [Status:Status, "Completed"], [Budget:Budget, "Fixed"], [Quarter:Quarter, "Q1"]). It is also cross referencing month and year, but I am only struggling with the phrase portion of the formula. Anytime you are unclear about the syntax of a formula, if you open the formula window the help wizard shows you the expected format. @Paul Newcome Perfect that fixed it! It's been a long day =SUMIF({Column Housing Text}, CONTAINS("RSMAS",@cell), {Column You Want To Sum}), =SUMIF({Completed Request - Safe Return to Campus Range 2}, CONTAINS("RSMAS", @cell)). The formula will sum amounts if the status is complete, the budget is fixed and the quarter is Q1. Is it possible for you to provide the formula you used (the one that only provides the first column)? Good callout thanks. Let's call this helper column "Number", =(LEN([your text string column]@row) - LEN(SUBSTITUTE([your text string column]@row, "D", ""))) / LEN("D"). I appreciate it. It's not searching for exactly the 360, but any finding of those characters. 2022. When you say it doesn't work, are you receiving an error or an incorrect result? =COUNTIFS(Department:Department, CONTAINS("Marketing", @cell), Start:Start, IFERROR(MONTH(@cell), 0) = MONTH(TODAY())), @Paul Newcome Whoops. How can I only search for the exact number 360 and not the individual characters that make up 360? For example if a string contains "Apples Oranges Pears" and I was looking for "Apples", your code would return "6" because there are 6 characters in apple. My apologies. =SUMIFS(Quantity:Quantity, Unit:Unit, @cell = "text"). =SUMIFS([Q1Rating-Analytics and asset to project level]@row:[Q1c Rating:Partner view of assets]@row, [Q1Rating-Analytics and asset to project level]@row:[Q1c Rating:Partner view of assets]@row, ISNUMBER(@cell)). Thanks for the help Paul. I'm not clear on why you repeated the range in this formula. It's only picking up the cells that JUST have the "Marketing" department. Is there a way to have it look at the values between each "," or will it only see that as a full text string? All Rights Reserved Smartsheet Inc. https://community.smartsheet.com/discussion/comment/286773#Comment_286773, https://community.smartsheet.com/discussion/comment/286832#Comment_286832, https://community.smartsheet.com/discussion/comment/286862#Comment_286862. This is so that it looks into each cell of that column to see if it has that value. This formula will work for both Grant and Contract (5000 / 1500 results), =SUMIFS($[Column4]$4:$[Column10]$4, $[Column4]$3:$[Column10]$3, FIND([GL Name]@row, @cell) > 0), I used absolute references so the formula can be copied to the next row. Thanks so much!!!

=SUMIFS(SumRange:SumRange, Criteria:Criteria, CONTAINS([Criteria Range]@row, @cell)). That would make it easier to help.(share too,[emailprotected]), Workflow Consultant @ Get Done Consulting, Andre Star | Workflow Consultant / CEO @ WORK BOLD, W: www.workbold.com | E:[emailprotected] | P: +46 (0) - 72 - 510 99 35. I've tried it and it's unparsable when I enter this as well as if I don't repeat the range. Your proposal appears to count the specific characters within a string. I'm doing the reverse. You didn't insert the sumifS PLURAL Please add the s. You should be able to literally copy paste my formula into your sheet. With contains doesn't it need to look at a range of cells? (it should be 5). Wrote the below formula, I would prefer that "Apricot Puree" actually reference the contents of a cell, but no luck here using either method. I don't get an error. This formula works: =COUNTIFS(Department:Department, "Marketing", Start:Start, IFERROR(MONTH(@cell), 0) = MONTH(TODAY())). Example: Cell 1 is "A1", Cell 1 is "B2" etc. Use individual SUMIFS for each column and then add those SUMIFS together. Try substituting HAS for CONTAINS. If I have that as a result of a formula (the same "360" ) it doesn't work. The only way I have been able to come close to that is with a JOIN to join the entire range into one big cell and count that. I've even tried wrapping it in SUM. Any ideas? It will return "True" if it found the character or string. Everything should be searching for and counting within a range (column), not a single cell. If it is going to take more than a few minutes I have to let it sit for a while until I can work on it more. It looks for a direct match. Can someone help understand how I might use "find" in this scenario? So below I have the two columns circled I am talking about. So you say "{Range}, @cell = 1". I too would suggest leveraging the LEN function similar to @KDM's solution but with a couple of tweaks (primarily moving the UPPER function to the text string as opposed to "D"). @Paul Newcome Yeah I had that thought also but the multiple codes is also a join() formula that collects results from another area to create the string. I cannot figure out how to create a formula that will sum the total hours in the Quantity column that match a specific text entry in the "Unit" column., Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? Only checking alerts and not able to help with the more complicated solutions. You are, instead looking for non-blanks cells, then ISBLANK() is what you need. I would like to figure out how to sumif Row 3 all columns containing the term "grant" so that I can get $5000, =SUMIFS([Column4]4:[Column10]4, [Column4]3:[Column10]3, "Grant"). If you are wanting to count a string that is longer than 1 character: =(LEN([Column Name]@row) - LEN(SUBSTITUTE(UPPER([Column Name]@row), "AB", "")) / LEN("AB"). All Rights Reserved Smartsheet Inc. Sometimes I think commas are more problematic than parenthesis. I want to add the capacity total for the end column by month, so if we are referencing October 2020 it would only pull capacities into the total where the corresponding cell had October as an option. Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely. If you hover on the different elements within the formula you wrote, you will see the help text highlight in the wizard, indicating where in the formula syntax smartsheet believes you to be at.

Example looking for the number 1 within cells containing 1, 2 and 3 with a result or sum of 1. The value in cell [1]2 is populated with a cross sheet formula the capture the names of our PM's. Yes, that worked @Paul Newcome . Note that I change your range order. No worries. I have one column set to multi-select with months (August 2020, Sept 2020 ect).

Typically you use an SumIf to search a set of cells (range) and it's contents for a single criteria code. HAS is designed to look within multi-select dropdown columns. If you're looking in a multi-select column for a specific value, I would actually use HAS Function instead. My workload has increased to an absurd amount, and I haven't had tie to hang out here as much as I used to. I would rather have it search multiple columns (in this case 4) and find the phrase I am looking for and then perform the rest of the formula. If the value Jacket is contained within a cell of the Clothing Item column produce the value True. =Countifs(range, criterion)+countifs(range, criterion) + Countifs(range, criterion). I didn't try to do that in one formula- I joined into a helper, then counted. I missed the SUMIF part. From your post, I picture your data being arranged similar to. =(LEN(JOIN(COLLECT([Column name]:[Column Name], [Column name]:[Column Name], CONTAINS("Apples", @cell)), "/")) - LEN(SUBSTITUTE(UPPER(JOIN(COLLECT([Column name]:[Column Name], [Column name]:[Column Name], CONTAINS("Apples", @cell)), "/")), "Apples", "")) / LEN("Apples"). It sounds like ISBLANK(@cell) will find blank cells, but I want it to find the opposite- those with numbers and letters. I'm also thinking about the logic in the following way since the only value in the range that is not a number is N/A but can't get the formula to work across cells. Haha. Only checked this one cause I got the alert. =SUMIF([Q1 Rating-Ingesting assets from multiple sources]@row:[Q11 Rating Support]@row), [Q1 Rating-Ingesting assets from multiple sources]@row:[Q11 Rating Support]@row), ISNUMBER(@cell)), The format of the SUMIFS is (Sum range, range1, criteria1, range2, criteria2, etc).