-
Counting Multi-Select Column value, based on another Column value
Given the following Grid - I am trying to count all instances of 'Apple' in a Multi-Select Column ('Fruit'), but only when another column ('Market') has a value of 'Market A' So in the above example, the answer would be 1, as whilst 'Market A' appears twice in the 'Market' column, 'Apple' only appears in the first instance…
-
Counting # of rows under a parent row that meets a certain criteria
Hi! I am trying to figure out what formula to use in the below situation: My sheet has parent rows that determine various phases of a project. Each child row has a status (in progress, complete, etc.) for each step (step 1, step 2, etc.). I am trying to figure out what formula would count how many "in progress" child rows…
-
IF Function Resulting in Specific Symbol Given Specific Inputs from other Columns
Hi all, Wondering if I could get some help on writing out a function. I know that it will probably be some sort of advanced and lengthy IF function but am confused on how to write it out. I have a Smartsheet that is used to track data inputs from a form. This data is related to a specific order coming in. I would like to…
-
Is it Possible to Use INDEX-COLLECT When a Criteria Field is a Multi-Select Dropdown?
Hi, all; I'm hoping you can help me with this one. Here's my boggle: Sheet A collects Milestone and Parts data from a form and datestamps each entry. Sheet B has columns (Milestones) and rows (Parts) corresponding to the data entered in Sheet A, and the cells pull the date for each entry from Sheet A. For simplicity, we'll…
-
What is the correct COUNTIFS/TODAY formula for past and future 90 days?
I am trying to COUNT the tasks assigned to departments in the past/future 90 days. I am using sheet summary fields for this data. These are the formulas I have come up with but they are not accurate. =COUNTIFS(Department1:Department72, "fsb", [Start Date]1:[Start Date]72, <=TODAY(-90)) =COUNTIFS(Department1:Department72,…
-
Mileage reimbursement Help
I have automated an expense report. This worked great as I did an index/match on the date the expense was for, compared to the year, and reported back the mileage reimbursement rate. =IF(ISBLANK(Miles@row), Miles@row * 0, Miles@row * (INDEX({Mileage Reimbursement master Range 1}, MATCH([F*Year]@row, {Mileage Reimbursement…
-
Flag warning if date is after another within same column
Hi all, Currently having issues with this: I need to find a way of flagging a warning (that'll just say "WARNING") in the thirteenth row of the date column if any of the dates in rows 2-12 are after the date in row 1. I am also struggling to find a concise way of flagging a warning in row 14 if there are any duplicates in…
-
Nested IF with INDEX (MATCH)
Hi there! Hoping to get some help solving an index match issue I am working through. I want to use a formula that will pull a rental rate, from a rate card, based on the rental duration. There are 8 different rental rates possible for each item. Currently I am using, =IF([Rental duration cheat]@row = 1, INDEX({1 Week…
-
Need a Clear Example of Using INDEX and MATCH in lieu of HLOOKUP
Hi. I've found plenty of examples of how to use INDEX and MATCH in place of VLOOKUP in SmartSheet, but no matter how many help topics and forum Q&As I've searched, I cannot find a clear example of how to set up INDEX and MATCH for an HLOOKUP equivalent in Smartsheet. Can someone please help?
-
Populating Entire Columns Referencing Another Sheet
Hi all! Over the last week or so I have been trying to build a report that shows average tasks completed over a given 12 week period. So far I have been successful in creating the formulas for these, which have been working on dummy data. Now I would like to process live data from another sheet. Well ideally I would…
