-
Stop counting blank cells
I have a column of checkboxes. Currently there are 16 rows with data but 18 rows total. I have a column setup for some of these called Skip which checks a box off so another function will know to skip those (Currently two rows are being skipped which is giving a count of 16). However when I want to count the blank…
-
Perform formula if value is found on another sheet
Hello Smartsheet users, I am attempting to create a sheet that will provide me with Weekly Usage totals of an inventory item so I can use the data to create better forecasts and projections. There might be a better way of doing this but as of right now my plan is as follows: Active Sheet contains all of the current totals…
-
Nested IF to populate Cell
Hi, I have a nested If formula I am having trouble with, =IF(Category@row = "Drink", "Lemonade, Tea, Water, Soft Drink", =IF(Category@row = "Toping", "Pepperoni,Steak,Chicken", IF(Category@row = "Fries", "Cheese, Chili"))) I have a drop in the Category column. If I choose the "Drink" option is is working fine, but if I…
-
Compare two sheets; flag a name that has not submitted a form
I'm trying to flag individuals who have NOT submitted a form, but am coming up short on the formula. Sheet A Memberlist has a list of all the individuals that need to submit the form. Sheet B Feedback is the form data after it has been submitted. I want to use Sheet A Memberlist as the reference for who is supposed to…
-
COUNTIFS formula for projects archived this year for a contact in a multicontact column
I'm trying to write a cross-sheet formula that counts the number of projects one lead (eg, Ben Canada) has archived in a given year. Here is a formula that works if Ben Canada is the only contact: =COUNTIFS({Rollup Sheet Example-Lead}, CONTAINS("Ben Canada", @cell), {Rollup Sheet Example-Archived Date}, YEAR(@cell) =…
-
Summarize child information in parent row?
Hi - I'm trying to write a formula that would group and summarize information from children rows into a cell in the parent row and I keep running into issues. I have a "Dropdown (Multiselect)" column for "Work type" that has the available values of Web Design, Copy, Video, Animation. Any row can be one or any of those…
-
Conditional Formatting with #No Match
Hi everyone, I've been working on conditional formatting utilizing the #no match on a formula (a vlookup that doesn't have a reference so we can add). I've like to have some cells highlighted (rows 1-12) when "no match" occurs, which is a possibility on the conditional formatting options, but highlights aren't happening.…
-
Formula to check box in sheet A based on data in sheet B
Will you help me write a formula in Sheet A that will do the following three things? 1) Look for a checked box in Sheet; 2) if the box is checked, then look to see if the cell in the same row - next column contains "100%"; 3) if both are true,
-
How do I extract data from a column that represents a week or month?
Lets say I count apples sold and I input the data in column [Number of apples sold] which is next to a date column. Now I want to find out how many apples I sold in a given week. I use this formula: =SUMIFS([[Number of apples sold]:[Number of apples sold], Date:Date, WEEKNUMBER(@cell) = 44, Date:Date, YEAR(@cell) = 2021)…
-
Formulas for COUNTIF on Fiscal Year
Hi Smarties, I would like to count the number of applicants based on the Stage as below. However, I would like to only count if they fall under this fiscal year (July 2021 to June 2022). My current formula is split to three rows (Taking Successful (R5) as an example) This Year : =COUNTIFS({Created Date}, >TODAY(-365),…