-
Countifs with multiple variables
I am trying to automate a tally of which SME's have worked an event with which countries. Ultimately, I created a column that lists the countries that participated in the particular event (ideally I would like to keep multiple countries in one cell per event), and hoped to use the "joined" column to reference the SME's…
-
One column to read multiple if/and criteria and populate
O.K., I have a detailed question so please bear with me: I currently have the following formula that works at the moment: =IF(AND(ISTEXT([OPEN ISSUES]11), [STATUS 3]11 = "Closed"), "", IF(AND(ISTEXT([OPEN ISSUES]11), [STATUS 3]11 = "Open"), "Open Issue", IF(AND(ISTEXT([OPEN ISSUES]11), [STATUS 3]11 = "Pending"), "Open…
-
Counting Parents on a sheet ignorning children.
Hi everyone, I have a sheet in which I have parents and children. I only want to get a count of the parents. I have seen other posts that recommend adding a blank line at the top and making everyone a child. Is there another way around this? I have attached a screen shot.
-
How to use Index/Match instead of vlookup still using sheet references
Hello We have started to build a large master sheet using vlookups (mostly countifs and sumifs) cross referencing our other sheets but have quickly run into the 25000 limit. It has been suggested that by using index/match instead we can avoid this, can anyone help how this works? some example of formula we are currently…
-
Return Text based on a date
I am trying to write a formula that will return the text "Current" or Expired in the INS status Column based on whether the dates in the GL, AUTO and WC dates are past today's date. Hitting a wall. Attached snip of the sheet Thank you in advance for any help!
-
CountIFS formula including OR
Hello, I am trying to create a complex formula using ranges and inside the COUNTIFS i need to include OR functions, but when i do the below it comes up with error: =COUNTIFS(OR({Current Jobs Completed Hour}, <7, {Current Jobs Completed Hour}, >18), OR({Current Jobs Created Hour}, <7, {Current Jobs Created Hour}, >18),…
-
checkbox if statement based on date and multiple drop down options
I'm trying to set up a formula that will check an "At Risk" flag based on a date and several drop down options. Example for row 7 : If the [Ship Date Estimated]7 is less than Today and the [Status]7 does not equal "Shipped" then check the flag or if the [Delivery Date Estimate] is less than today and the Status does not…
-
Formula to automatically input total number of rows
Hello! I'm hoping one of you smart people can help me... The "$A$70" value in the calculation below is the number of rows in the sheet I want to replace the "$A$70" value with a calculation (or whatever it's called in this case) to automatically update the "70" dynamically with however many rows in are in the spreadsheet…
-
Formula
Hello everyone, I'm new to smartsheets and so far my experience has been positive but I'm heading into areas that go beyond what I have learned so far, I used basic formulas to do counts using =SUM, and =COUNTIF but I only have been able to use it to do basic counts. I need a formula that will look at at multiple columns…
-
Formula to convert numerical values in Column A with LOW/MED/HIGH
Hi there, I hope someone with a big brain can help me solve this issue I'm having. Desired Result: I'm trying to fix a formula that will automatically input the Values "LOW", "MED", or "HIGH". I would like to be able to copy the three (corrected) formulas I have into each spreadsheet I need to work with, without having to…
