-
How can I calculate the number of years between 2 dates where each calendar year must count as 1 yr?
I need to calculate the number of years between Contract Start and Contract End, where the years must be counted as whole if they are within a calendar year? This calculation is being used to create Purchase Orders that must have a single line for each calendar year, irrespective of when during the year the contract starts…
-
Count Rows in Reports
Currently there is no way to capture and report the number of rows in a report (or across multiple reports). It is common in dashboard reporting to create a metric (or card visual) to count the number of specific items in a table by simply allowing an application to count the number of unique or distinct rows. For example,…
-
Combining two formulas to filter data
I need to create a formula to count any lines that reference "GEMS Point", that will further filter only for any references within a given month. I have the following two formulas that are working perfectly: Formula for total by GEMS Point: =COUNTIFS({GEMS Point}, [Primary Column]@row) Formula for January programs…
-
Using countif to select an option from a column
I have multiple selections in a column and when I tried to use countifs, it will not pick up the label that is combinated with other options. For example, in Column B, I have following look up values - Role 1, Role 2, Role 3, etc, but some rows have two or more roles - so in one row, it will say role 1, role 3. In another…
-
Counting checkboxes complete by location
Hi, I think I'm close but I can not seem to get this formula right, so coming to the big dogs for help :) This is my refrence sheet, and I'm trying to create a data sheet to collect the numbers & display on a dashboard. I need it to tell me how may cards are complete & how many are yet to be completed by location…
-
How to correctly refer to another sheet for COUNTIF?
Hello, I am new to smartsheet and trying to create a dashboard for my source sheet. I have created a blank sheet to help add all my formulas. 1. I have a source sheet - "MySheet" 2. It has a "Category" column and is a multi-select dropdown list 3. The values are Engineering Roadmap Process Change Administrative Hiring…
-
How to count total checked in report instead of just total number in report?
I am trying to use the report to summarize some data to ultimately put in a Dashboard. In the screenshot I am actually trying to count the total number of T4C completed which would be indicated by the check mark. Instead it is currently counting the total number in the report, checked or unchecked. I am using a report for…
-
Mastering Parent-Child Row Relationships: Solving the '#INVALID DATA TYPE' Dilemma in Health Status
I am working on a sheet where I want the parent rows to reflect the health status of their child rows. I have defined four categories, which are "No Risks," "Some Risks," "Overdue / Risks," and "Canceled / Done / On Hold." I have created a formula to achieve this, but I want to establish a rule that only when ALL child…
-
If a value from Column A is found in Column B, can the occurrence be shown as "true".
Hello, I am looking for the easiest formula to help show when a value from Column A shows up in Column B. If it is possible, I do not want to enter each value found in Column A in a formula, as these values are actively changing. (Note: The list "Column A" is also extensive so manually entering each Active Project would be…
-
Sheet Summary Question
Hello all, I am trying to do a sheet summary to show the Active Dealers vs Non Active Dealers that are running campaigns. An active dealer will have a monthly total greater than 0. However the Monthly Total Column in the smartsheet is a referencing information from another smartsheet using a Vlookup. I am trying to do a…