-
Gathering data from 2 smartsheets into metrics sheet for dashboard
I am trying to take this formula and get the data 'CAD' from both smartsheets and place the amount of times in total that it appeared What am i missing in my formula? =COUNTIFS({Behavior Analysis: 1S Range 1}, HAS(@cell,"CAD"), {Behavior Analysis: 1SShop Range 1}, HAS(@cell,"CAD"))) Anyone have any suggestions?
-
Complex Lookup
I have a main table that, given the team(s) involved in a project, needs to look up the contact info for members of all of those teams. ([Teams Involved] is a multiselect dropdown, and [Members of Involved Teams] is a contact list where multiple contacts are allowed.) It is to get the list of team members from a lookup…
-
Sum formula not adding correctly
I am trying to sum up columns but its giving me an output of zero when the total should be 25. Can anyone help?
-
IF Formula
Hi I would like help on a formula to score time criticality. Ive tried the below but its coming up with errors. I also would like to add a formula that if there are any negative numbers such as -201 then this would show as "10" Formula i used below but not working =IF([Time Criticality]@row <= 30, "10", IF([Time…
-
x-sheet formula help
I am attempting to write a cross sheet formula on sheet (call it sheet a) that returns the value of a column on reference sheet b, based on finding a string of text found in anther column on sheet b that matches a Project ID on sheet a. Here was attempt 1 that didn't work =INDEX(PERFORMANCE AMOUNT), MATCH([PROJECT ID]@row,…
-
Networkdays formula between today and go live date
Hi, I would like a formula to calculate the number of network days between todays date and a go live date and return a negative if go live is in past and positive if go live is in future eg todays date 24/03/25, (go live date) 29/03/25 = 5 days Here is the formula im currently using =TODAY() - [Requested Go-Live]@row on my…
-
LOOKUP addresses and format as CONTACTS
I have a working formula =JOIN(COLLECT({EMAIL}, {AGENCY}, Agency@row), CHAR(10)) This gives me all the emails for an agency in a single cell, with a return between each address. I need to transfer the grouped emails to another sheet in a CONTACT COLUMN and have them recognised as contacts so that I can set a filter in…
-
Help with Countifs and not contains formula - no longer working
I have been calculating how many submissions in a month in one column (column A), and cross-referencing against another (column B), to ensure I'm not counting items that have a particular word in column B. I used the AI help (when it was available) and the following was working: =COUNTIFS([Date Received]:[Date Received],…
-
Countifs off by one
I am trying to get a count for a date range, but am getting a number that is one less compared to the filter. Current Tuesday Formula: =IFERROR(TODAY() + (3 - WEEKDAY(TODAY())) - IF(OR(WEEKDAY(TODAY()) = 1, WEEKDAY(TODAY()) = 2), 7, 0), "") Previous Tuesday Formula: =IFERROR(TODAY() - 7 + (3 - WEEKDAY(TODAY() - 7)) -…
-
Force upper case on a column
Hello Everyone, I got a question. Is there any way to force upper case on a text column? I have a column where users enter information using iPads and it will be very helpful if the cells will automatically force to have upper case font in it. Thanks