-
If Formula with a Dropdown List
I have a column titled Status that includes the following drop down selections: Not Started, In Progress, On Hold and Complete. I want to write a formula that based on the drop down selected, another row would populate the following automatically: Not Started = 0%, In Progress = 50%, Completed = 100% and On Hold = ON HOLD.…
-
Formula for several "If/Then" support
Hello Community! I have tried googling this for excel and I'm just not getting my formula to calculate what I want/need. I've created formula for equating a number range to a letter, but I can’t figure out how to get the letter cell to not populate if nothing is entered for a numerical value (left blank). I don't want to…
-
Last 30 days range
Hello, I have looked through the forums that are currently open and could not find a formula that would fit my current needs. I am using the following formula now: =COUNTIFS({Pre Trip Completed}, <3, {System}, "ITCMW", {JBO Type}, "Overhead", {Date}, IFERROR(MONTH(@cell), "") = 8) This formula is pulling data by month (8=…
-
COUNTIFS with date referencing a date range
Hey all, I have a time off tracker for our installers and their skillset is either level 1,2,3 (picture 1). I need a way to show who is taking off in reference to the date column by level (picture 2) but not sure how to build this formula with a date range. Also, I have it set where if they request one day off, the "End…
-
Project Phases of Development
Hello! I am trying to find a way to show the development stage of a project so it can be rolled into a report. Basically if all of the tasks in a section are complete, then the development stage would be the name of the next header task. For example (see image), if "Project Planning," "Drafting," and "Developing" are all…
-
Cross-Sheet Formula Help
Hello, I'm trying to create a cross sheet formula that looks at the content of multiple columns in a row on one sheet and looks to another sheet to confirm those values are present, and then returns a value in that sheet. I've tried VLOOKUP and an IF statement, but I'm not able to get it to work properly. FYI - within each…
-
How to count "Contact" cells with multiple contact per cell
There is a contact column where multiple contacts per cell are allowed. How can I count the total number of rows that contain a specific name? Is it possible to combine COUNTIFS & CONTAINS? =COUNTIFS({Responsible}, {Status}, "NEW", {Responsible}, CONTAINS("Dmitry Gorobets", {Responsible})) The formula returns 0, but the…
-
Formula to keep Date when another date column is blank
Hi, I am trying to create a formula that will keep the existing date in the cell if another column is blank and if there is a date then I want to add 365 days to the other column and return it in my NEXT CONTACT DATE cell =IF(TODAY@row, "", [NEXT CON TACT DATE]@row, IF(TODAY@row, TODAY@row + 365)) My Column Headers are:…
-
UPPER formulas
Is there a way to set a formula so all text in the column or sheet is upper case even if someone enters it on a form in lower or title case?
-
SUMIF/AVERAGEIF across rows not working
I'm trying to SUMIF and AVERAGEIF across rows in my sheet, and I keep getting an #INVALID VALUE error. My table of data includes columns across for each employee, and each row contains the employee's hours worked. The screenshot below shows the helper row (row 3 in my sheet) that specifies whether each employee is a…