-
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 /…
-
How can I use VLOOKUP to find data from multiple sheets and combine it in another sheet?
I have an existing database of suppliers/vendors set up in a sheet, and I'd like to automatically show if they're currently working on a project and which project they're currently working on, as well as the dates they're working on the project within that sheet. I'm creating a supplier sheet per project which shows all…
-
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,…
-
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.
-
Formula References vs Lengthy Formula Updates
I maintain a lot of large sheets - at least 9 per FY with around 190 columns and up to 1,500 rows per sheet. Within these are many transit timeline formulas for off-shore shipments. These are often in flux, and when something changes, I need to go through each sheet and update the numbers in the column formulas. This can…
-
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",…
-
Conditional Formatting for Cells Containing Formulas
I am working on a project milestone sheet and when projects are first entered the milestone dates are calculated using formulas, based on the start date. As the project gets closer, we will have users manually overriding the formula with the 'actual' date if it is changing. For this solution, we are not using column…
-
INDEX MATCH with MAX
Hi Scenario: Reviews are required for people across the firm and some may have more than one review in the year. I need to pull through the date of the last review. My current formula is: =IFERROR(INDEX({01. File Review Range 2}, MATCH(EEID@row, {01. File Review Range 5}, 0)), "") This index's the last review date and…
-
Sum between two dates but left blank if there is no date in one of the columns
Hi there, I am looking for the formula to find the number of days it took between two dates only if there is a date added in Column A. I have used and this worked great to get the days but if there are any blanks in Column A it comes back as a 0 and I am looking for it to be blank instead. What do I need to add to make it…
-
COUNTIF or VLOOKUP capabilities?
Hello friends! I have a column of data in a grid, formatted as a dropdown with specific text to select. I want to create a formula wherein each instance of a particular dropdown selection is counted-see generic example below. I've tried a couple of permutations of "COUNTIF" and VLOOKUP" to no avail. Is this something I…