-
Using CountIf to get values from multiple sheets
Hi Smarties, Just out of curiosity, is there a simpler formula when consolidating data from multiple sheets with the same column name? For example, I have sheets Test 2 and Test 3. Both have status columns and I would like to count how many In Progress in my roll-up sheet. It works when I do the following formula…
-
How do I count how many projects are "In Progress" or "Completed" ?
HI There, I feel this should be easy to figure out but i cant seem to! PLEASE HELP?! I have a column called "STATUS" , I have a drop down menu with the following under Status STAUS On Hold In Progress Cancelled Completed How do I show / count how many projects are "on Hold" or "Completed" or "Cancelled" Ideally this will…
-
Display date and time in India time from GMT
Assuming Created time is captured inGMT datetime, I would like to display Indian standard time in another column. I referend few timezone conversion samples, but its not working if the timezone time with excess of 30 min. India timezone is 5 hrs 30 min ahead of GMT, formulas discussed in the forum works if GMT+5 and not…
-
Index/Match Cross-Sheet Formula Help Request
I am stuck on what probably is a fairly simple cross-sheet INDEX/MATCH formula but this evening, for the life of me, I can't seem to crack it. It may be that I need to sleep on it, or it might be that in a few days I will be leaving on vacation for two weeks and need to get this complete and over to my supervisor before I…
-
I want to count a cell if it contains any kind of text "*"
Hi there, I'm trying to find how I can count how many times a cell contains anything within a column. I know there's no wildcard like "*" in Smartsheet. Is there a workaround without inserting a specific text? I'm trying to replicate the formula below from Excel into Smartsheet. =COUNTIFS({Column X}, "Purple", {Column…
-
Status Circle Automation
Hi, I am using status circles for a high level status report. Is it possible to automate the 'Overall' Status Circle based on the colors in the other measures (budget, timeline, scope, KPIs, value). For example, if 4-5 of the metrics are green, Overall is green. If 3/5 are green, Overall is Yellow. If 1-2 are green,…
-
Find the row with the earliest Created (Entered) date that matches certain criteria
This one was a challenge. I thought I had it solved, but an erroneous value showed up, necessitating taking the formula back to the drawing board. The requirement: Show me the oldest Bill of Lading number that does not have a status of "Closed" or "Canceled." In another cell, show me the full Created date and time of that…
-
Duplicate Dates Across Multiple Columns
Hello, I'm trying to highlight duplicate dates with conditional formatting across various columns to avoid conflicts. Is there a simple way to do this? Sample Data is below. Thanks in advance.
-
Count # of Parent Rows in a Sheet
What formula can I use that simply counts the number of parent rows in a single sheet? My scenario: Each parent row is a different customer. I want to know how many active customers a particular PM is working with and link that number to a metric widget on the PM's internal dashboard.
-
How to Summarize the Assign to: Contacts in a Report
Hi There I see there is plenty questions relating to this topic, but what ever I try doesn't work. Here is what I'm trying to do: I want to create a Report on Multiply sheets. Each sheet has different Contacts assigned. Now I'm stuck on the Sheet Summarized Option. Here is my Screen Shot 1st Question - Under Summary Sheet,…