-
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…
-
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!
-
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…
-
Formula With a date
I am having an issue with creating a formula that will count a column if a row has a date in it. It can be any date just as long as it has a date. PgM Status is the column with the date right now I'm just counting the section that has the dates but would like to create a formula, so I don't have to keep adjusting in the…
-
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 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…
-
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.
-
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…
-
Calculating an Average based upon a value in other column
I have a calculation to calculate the number of days between two columns. =IFERROR(NETWORKDAYS([Date Submitted]41, [Go Live Date]41), " ") It works great to return a blank if the customer has not gone live. Then I have formula to calculate the average of the cells. =AVG([Number of Days to Implement]12:[Number of Days to…
-
Vlookup/cell linking
Hi All, I've been having a lot of trouble with a vlookup formula that I have linking a sheet with ALOT of columns--just under 200. There seems to be a limit to the number of columns to 'look at' (which is 85.) This is the last formula that I have which works. =VLOOKUP(TODAY(), {1. DOR (Pull-out) Range 1}, 85, false) I…