-
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) =…
-
COUNTSIFS does not Contain
Hello, I've put together the following formula, to count certain items in a different sheet that don't meet certain criteria, please see below. =COUNTIFS({SE-Region}, Region@row, {Status}, <>"Completed", {Status}, <>"Canceled", {Status}, <>"Duplicate", {Summary}, <>CONTAINS("Onboarding", {Summary})) The formula works until…
-
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,
-
COUNTIF Formula in Multiple Dropdown Box but Exclude a Word
I’m trying to create a COUNTIF from a multiple drop down column (referencing another sheet). Two words are similar: Active and Inactive. If I use CONTAINS, it counts both Active and Inactive: =COUNTIF({Certified Roster Range 1}, CONTAINS("Active", @cell)) If I use HAS, it counts when Active is the ONLY item listed in the…
-
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),…
-
What is wrong with my automated colour column formula?
I am looking to automate the colour “status” for my rows based on the health of the action - a calculation based on the ‘% Complete’ and the ‘Due Date’. The goal is to demonstrate a contextual health status. For example, even if an action is only 10%, if the due date is still 6+ months away, the action is still Green due…
-
Scoring Formulas
I'm looking to convert a process to smartsheet, but am trying to determine if there is a formula to make the concept work. Currently I have a form which asks a series of questions - each question has a single select option of "yes (description)" "no (description)" and "n/a". For each question, there is a score associated…