-
Summary sheet count when filter on
Hi, I am currently using a summary sheet. I have used the following command in one of my fileds =COUNTIF([% Complete]:[% Complete], <0.99) But i want to count when one of the columns has a filter. This command still counts all even when i have the filter on / off. thanks for your help
-
"Equal" cells
Hello Community, I have a regular project plan with several tasks, milestones etc. I was thinking of tiding it up by creating a collapsible section in the very beginning which will contain only the milestones. My idea was to use the simple "=" function so what I did was to simply clink on the cell in the Milestones section…
-
How to automatically put formulas in the new rows that will come in from a form
Hi, so we have three sheets in the company, and one as a master sheet where it gathers all information from sheet 1,2,3. Everytime I will be inputting a new information on a form, the formula doesn't apply on that new row. is there a way I can automate this that if there is a new row, it will have the formula of the one…
-
Use IF/AND or INDEX/MATCH?
I have a sheet (top) where Site Code and Sub-GL are drop downs. There are hundreds of corresponding site/sub-GLs listed in my reference sheet (bottom). I am looking for the correct formula to return value of "2021 OP2 Total" when Site Code and Sub-GLs are selected match a line in the reference sheet. I can't seem to make a…
-
Calculate Variance in Days
I need to create a formula that calculates the total number of days an employee is in training. One cell in my sheet is the Training Start Date. One cell in my sheet is Today's Date I need this cell to calculate the number of days between these to days. I tried =NETDAYS(start_date, end_date) and it give me #UNPARSEABLE
-
How to calculate a COUNTIF when using a date 01/01/2020 month to date
Good Morning Everyone, I am trying to create a formula that pulls from a master spreadsheet that calculates the number returned based on a date from the data sheet. I have columns that indicate month to date, September, August, July, June, May, April, March.... =COUNTIFS({LPSC tickets Range 1}, "Production", {LPSC tickets…
-
Sumifs with Multi Select DropDowns
Hi all, this is very simple, but I am pulling my hair out! I am trying to do a SumIfs, where one of the criteria is a multi select dropdown. My first attempt is =sumifs({column to be totaled}, {Multi select Column}, Option 1) If I do this, then it only Suns when Option 1 is the only option, not where option 1 and and any…
-
Count IFS
I am trying to "countif" the Last Name and the First name in Summary. I figured out how to count the last name =COUNTIF([Last Name]:[Last Name], "Roman") but what do I have to do to capture the first name if it is in a different cell? =COUNTIFS([Last Name]:[First Name], "Roman" "Kate"). I can't figure out how to capture…
-
Date updating to Next Day using Index, Match, Max, Collect Formula
Hi, I'm working with two Smartsheets. (1) One sheet collects the data from a Smartsheet form. I added two columns to this sheet using the Auto-Number/System Feature (under Edit Column Properties). This features stamps new row entries with a date/time (Created) and by whom (Created By). I'm curious about the cell surrounded…
-
Contains "Kansas" is returning TRUE when actually contains ARKANSAS
My State field is multi-select and users can select: Kansas or Arkansas or Kansas and Arkansas or other. I want to return a text value "Is Kansas" in a separate column if the State column contains Kansas or contains Kansas and Arkansas but will not return "Is Kansas" if the State column has only Arkansas (or other). When I…
