-
Sumifs w/ cross sheet references not working
Hello, I have a customer list and I am trying to use it to extract data from another sheet to get customer specific information. My SUMIFS formula to total the $$ for all projects with a specific customer is working. =SUMIFS({BidBoard Quoted Value}, {BidBoard Customers Bidding}, HAS(@cell, Customer@row)) But when try to…
-
I need to calculate % Complete
Hi, I am trying to calculate % complete with an IF formula because if it is marked complete I want it to say 100% and the current formula is tell me it is 3,098% complete because start and end date are in the past. Old formula: =(TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row) What I am trying to write…
-
Automation using reference cell
Hello everyone, I hope you're doing well. I'm facing a difficulty in setting up automation. In the project model we've adopted, one person is responsible for monitoring the project progress, and this monitoring is measured as the stages are marked as Complete. This calculation is straightforward with a formula that…
-
If(OR and Value formula issues
I have a check box that if checked I want my cell to be 0, but if it is not checked I would like it to sum up three other cells. What is wrong with my formula (Example #1)? I am also having an issue with values. If I have a formula that I am asking to return a "0" or "100" based on criteria (Example #2), I have to add…
-
CountIf formula issue with similar multi-dropdown items in cells
I am trying to count how many items in a multi-select drop down column fit the criteria to accurately return the number. My main issues are that the multi select columns have multiple criteria that need to be counted and that I have location names that area similar. Ex: TL1-6, TL1-60, TL1-61, ....TL1-69 I have tried:…
-
If then formula
How do I set up a formula so that if([column A]@row="something", copy the value in [Column B]@row to [Column C]@row,""). I appreciate any help you can offer.
-
Getting an Unparseable error with SUMIFS formula
HI I am trying to calculate how much we have billed out, but not yet collected. We have 4 columns for each payment, up to 12 payments total. I want to add the Payment amounts together, only if Payment Due date is in the past and if Payment REC is blank. I've been playing around with the below formula, but can't seem to get…
-
Need help getting the 'most recent' entry in a column (in the simplest format)
I have several sheets that take a doctoral student to unpack to figure out why they work and I'm hoping that Smartsheet has simplified so that I can pare back some of our formulas. One of the most common needs is for us to feed a 'summary' area at the top of the sheet with the most recent entry at the bottom. For example,…
-
=AVG(COLLECT Formula Referencing Another Sheet
Hello! So I have a master sheet copied into 2 other sheets where people will make updates to in several row sections. I want the master sheet to reflect the averages of what they select in each row section (in percentages) into the master sheet's identical section (sorry if that does not make much sense, not sure how else…
-
Sheet Summary Sumifs with dynamic date range and customer look up
Hi, I am having difficulty with my formulas referencing summary sheet cells. My goal is to indicate a date range in the summary sheet cells, input a customer, and have it a summary cell show the sum of all of the quoted jobs for that customer with in the specified date range. My customers are in a multi check box drop down…