-
COUNTIF Question
Hello We have a sheet that contains variations of words in a multi select column. For example; Lisa, Lisa 1, Lisa 2. I am trying to count how many times the word Lisa appears, regardless of the variation, i.e. Lisa 1 or Lisa 2. I've tried lots of different formulas but nothing is working; =COUNTM({Status Range 1}, ="Lisa",…
-
Formula to change value to a number, then SUM
Hello! I am struggling with this formula & determined I need to start from the beginning. I have created a quiz. It is a mix of multiple choice (A,B,C,D) and True/False (T,F). Each letter will have a number value which is then SUM'd for a final grade. Something like this: Question 1: A=5 / B=4 / C=3 / D=2 Question 2: A=0 /…
-
Resource planning from date periods
Hi everyone, I have a problem which I'm really stuck on. On about a dozen schedules, there are hundreds of rows, assigned to 'ID's' (ID1, ID2 or ID3) that have durations from 1 to 9 days. I need to be able to predict resource capacity for those IDs, up to 6 months in advance, by week. In this example, the ID starts the…
-
Formula to count unspecified word/phrase
Hi, Could anyone help me in finding a formula able to count how many times unspecified words/phrases come up in a column? Specifically: I have 1 column of a sheet where I am tracking the name of customers. What I would like to get is a numerical quantification of how many times the same customer is found in one column. So…
-
Disregarding N/A in Formula
Hi, I've been using this formula: =IF(SUM(VALUE(RIGHT([Overall Summary Cost]@row, 1)), VALUE(RIGHT([Overall Summary Schedule]@row, 1)), VALUE(RIGHT([Overall Summary Risk]@row, 1))) < 5, "Blue", IF(SUM(VALUE(RIGHT([Overall Summary Cost]@row, 1)), VALUE(RIGHT([Overall Summary Schedule]@row, 1)), VALUE(RIGHT([Overall Summary…
-
Is it possible to create reference to another sheet by its name?
Hi, I'm making a report/dashboard to display data from many other sheets. It's just simply internal links to the cells in other sheets. But due to the number of sheets is huge so I'm looking for a more smart way. Want to know if I have the name of these sheets, like "sheet 1", "sheet 2" ... "sheet X", any formula can read…
-
CONTAINS with Trigger Search
Hi all, Thank you in advance for the help. Below is what I am trying to achieve. The columns are as follows: Address and Device Type Explanation: When Device Type contains the word "Coin". This will use the address of the device type that contains "Coin". This address will then be checked against the address column until…
-
COUNT COLLECT
Hi all, I have a COUNT(COLLECT) formula that dosent seem to work. The returned value dosent match the actual number of Red Status projects under the portfolio named "X", also the column status is a symbol and not a word. In the formula i am using the refere to a spreadsheet. Help please.
-
Index/Match trouble with returning text value
My goal here is retrieve the Row 1 values(grey cell color) based on where the match cell (value = 14) in this case falls under. I added a row identical to the column header values to try to get this method to work but if there is a way to retrieve the column header for the top 3 highest values in a row please let know.…
-
Nested IF
This seems like a pretty simple nested IF statement but it's not ever hitting the last condition. Any insight would be helpful. I'm autogenerating an Asset #. I want the formula to look at the Asset Type and if it's a "Video" insert a "V" and the autogenerated number (this works), if there is something in the Suite cell,…