-
Struggling with a SUMIFS date range formula
For this use case, I need to sum the total hours (Range 3) for every entry that has a date between January 1, 2021 and January 31, 2021 (Range 1). The formula I have seems close but is still coming up as Unparseable. Ideally, what I want is an evergreen formula that can be applied to every calendar month in the year. Here…
-
Filling Columns based on a Selection From Dropdown
I am working on a formula and cannot get it to return correctly, I need to be able to select one of two options in a dropdown. I was using a vlookup and kept getting the N/A for all of them even if the condition was met. I think I missed an additional trigger. Regions: ABC and XYZ for the example. Based on which region…
-
Combination of free text and formula in one cell
Dear community, Do you think there is a way that will allow me to enter free text in a cell which has a formula in case the formula does not return any value? Or a workaround? I linked two sheets. Sheet 1: "Report No.1" - range, "Country+Disease1"(text)- match Sheet 2: "Report No.2" - my search value,…
-
CountIfs X does not contain...
I am trying to get a formula to function and am having trouble. =COUNTIFS({KBeaver Workload Range 1}, "Complete", {KBeaver Workload Range 3}, "1", {KBeaver Workload Range 4}, NOT(CONTAINS("E:", @cell)), {KBeaver Workload Range 4}, NOT(CONTAINS("M:", @cell))) This is attempting to count tasks completed in Q1 by looking for…
-
Prorate and Sum in one function
Looking for help on how to use prorate function by Month with one formula for the following data structure Raw data sheet: One row per project: Start date; End date; $ Metric rollup sheet Start of Month; End of Month; Month; Year; Prorated $: I know I can do this but not sure if I can prorate in the same formula…
-
Create Overall Health Cell from Health Column
I created a Health Column using the following formula: =IF(AND(Status@row = "In progress", [Due Date]@row < TODAY()), "Red", IF(AND(Status@row = "Not started", [Due Date]@row < TODAY()), "Red", IF(AND(Status@row = "Not started", [Due Date]@row = TODAY()), "Red", IF(AND(Status@row = "Complete", [Due Date]@row = TODAY()),…
-
Using NOT and OR functions in Sumifs
I am trying to create a sumifs function that sums up all of the expenses in a second sheet if they are not labeled "travel". I attempted it with an all-inclusive OR function and then tried it with the NOT function and neither seem to work. It seems that they need to look at cells specifically within the sheet and can't…
-
Adding an attachment to an approval
Is there a way to add an attachment to an approval request automation? I am collecting a document in a form and want to send it to an approver for review and approval. When I add the attachment field it gives the option of uploading another document not sending out the document I already collected. TY!
-
How to populate a column with single line summaries of data from another sheet
A picture says a thousand words and I have struggling on how to explain what I am trying to do with a picture. Top left corner there is a list of people who have submitted data via a form. It is possible for the same person to make multiple submissions. On a different grid (bottom left corner), I am totaling each persons…
-
Look up Function - Multiple Matches
I'm having trouble coming up with the correct function and am hopping someone can help. I have Sheet A that contains the data show in screenshot below. I also have a Sheet B, where I am trying to pull data from Sheet A, when I reference a 'Project #'. I want the name of the Salesmen & corresponding Amounts to show in rows…
