-
Moved: INDEX and MATCH across two sheets: a detailed explanation
This discussion has been moved.
-
Rolling metric data sheet for chart that is current month and last 13 months
I'm trying to setup a metric data sheet that will automatically roll (update) without constant manual intervention. I don't believe I'm doing this right. We have multiple different types of task and I'd like a count by month of each that are In Progress. I want to show this data in two charts on a dashboard: a bar graph of…
-
Countif with OR referencing multiple sheets
I would like to count the number of times a status is "Closed" on three different sheets. It can say close on either 3 of the sheets - just want to bring back the total on a summary sheet. Here is what I have so far but think my OR is misplaced: =COUNTIFS(OR({Sheet 1}, "Closed"), OR({Sheet 2}, "Closed"), OR({Sheet 3},…
-
Use IF/AND to find 2 values (TODAY's date and another cell), then return a different cell's value
How can I search a 'range' to return the value from Row 23 "Goal Date (PS)" (11/4/21) using an IF/AND based on Created date (TODAY) and Pod # (Pod 3)?
-
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…
-
Percentage with Multiple Criteria
Hello, I want to see if there is a more efficient formula to do this. There are multiple order lines and I want to find out what the percentage of shipped items for that order, but it seems like a slow formula. Any thoughts on how I can make this better? =IFERROR(COUNTIFS([Sales Order #]:[Sales Order #], [Sales Order…
-
Index Match Formula help
Hi all, I have a Sponsorship Grid which lists all the product ID#'s the sponsor purchased within a single cell (comma separated). On my list of list of products (separate grid) I want to be able to reference the sponsor using that product. If this was one sponsor to one product I'd use index/match. Any ideas for the best…
-
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) =…
-
Countifs Formulas
Good Morning, I am trying to count if someone was promoted in 2020-2021 which has a Check box column or TRUE, as well as their rank. The formula is populating 0 so I must be doing something wrong? For instance we promoted 10 people to Associate Professor, 8 of which are female & 2 male. =COUNTIFS({All Faculty List Range…
-
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…