-
How to use a formula for SUMIFS with more than 2 not contains criteria?
If I have 3 columns one is "department", the second is "description", and the third is "amount". I would like to sum up the total "amount" for only department-1 in the "department" column, that doesn't contains "ABC" or "EFG" or "JKL" in the description column. I am trying to use =SUMIFS({amount},…
-
How can I get my formula to return a phrase instead of #INVALID VALUE
I have a sheet that lists job openings. There is a column formula that assigns a "status" for each job by searching another sheet of applications. For example, if a job has 3 associates applications, and their statuses are "New" "Interviewing" and "Offer" it assigns that job a status of 'offer" because that is the furthest…
-
I would like to make an IF index collect
I am working on creating an IF(ISBLANK({cross sheet reference of column}), " ", Index(Collect({ NAME},{cross sheet Helper column}, helpercolumn@row,{cross sheet status}, "Status, {CrossSheetDate}, Contains("/",{CrossSheetDate}),1) I get an error for the contains part and the formula works without the date, but I am trying…
-
Order of client health based on 2 columns
Hey Everyone! I am looking for assistance on the best way to do this. What I want is to prioritize our clients whos health is at the highest risk so we can plan to focus on the top "problem clients" This is my conditional formatting if you need it Is there a way to list(numerically or whatever way would be best) worst…
-
How to add a prefix to a project ID
Hi Community, I am trying to create a project ID column that generates a prefix based on the project type. I'd like the numbers to go in sequential order for each project. Ex: abc001, abc002, def001, abc003, def002 Additionally, I would like for it to only generate a number if another column specifies "New Project." Any…
-
Creating sum in a column for a day
Dear community Struggling with this problem - tried already different approaches but was not successful. I want to create a sum of values per day in a table and then an overall sum. How can I get only one sum per day, like in the column "Result I want"? Thank you very much. Best regards Karol
-
Count how many within a year
Hi All, I have column title named 'Type of Request' and you can click multiple answers, so a cell can contain 1 or maybe 4 answers for example... . I have another column called 'Date Created' which is the date of the request. What i am trying to do is a formula that will tell me how many requests we have had within the…
-
Nested Criteria on COLLECT
I'm trying to get an income by multiplying quantity with Price. Price is located on another sheet so I have to find it based on type, route, and active date. Here's the formula that I have: =IFERROR([Economy QTY]@row * (INDEX(COLLECT({Price Range}; {Type Range}; "Economy"; {Route Range}; CONTAINS(Route@row; @cell); {Active…
-
Best formula for a complicated desired result?
Hello, I have tried many different formulas and even tried combining formulas but can't seem to get one that works in a way for syntax to be used as a column formula. Any help would be appreciated, and I thank anyone in advance for tackling this. The desire result is a formula that can be converted into a column formula…
-
On an Index Match, why is it returning a match when the match criteria row is blank?
Hi, I am trying to get a a person's region included in a sheet by matching their name in the Staff Column to the source sheet. However, when the row in the Staff Column is blank, I am getting a return from the first line of the source sheet. Is there a way to get the return to be blank if there is nothing to match in the…