-
#INCORRECT ARGUMENT error
Hi (again!) From other answers I've found here I'm guessing this is a parenthesis issue but I can't work out which ones. =COUNTIF({Clinical Trials : Accepted Range 1}, >=TODAY(), {Clinical Trials : Accepted Range 2}, HAS(@cell, "Blacktown")) Trying to sort current contracts (expiry date today or later) by research site ie:…
-
Pulling data dynamically from standard project folders
We found a template for multiple projects that uses a standard folder setup to keep Reports, Project Plan and Dashboards for each project within its own project folder... allowing us to have the separate lists of tasks and RFI's, rollups organized for each project rather than a forever-growing list of entries from old…
-
Formula to exclude expired dates
I'd like to count how many "current" contracts we have My reference sheet has a column for estimated end date, I just can't find the right formula to exclude dates that fall prior to todays date Thanks!
-
Formula to exclude expired years
Hi Hi, I'd like to count how many "current" contracts we have and also how many we have up coming close to expiry (say in the next 60 days) My reference sheet has a column for estimated end date, I just can't find the right formula to exclude dates that fall prior to the current date. And I have no idea how to write the…
-
INVALID DATA TYPE error
Hi, I'm trying to count the cells that match these criteria but am getting an invalid data type error. =COUNTIFS(OR({Tier}, "Experiencia", {Tier}, "Experiencia Plus", {Tier}, "Experiencia Plus Unlimited"), {Renewal Month}, 1, {Renewal Year}, 2023) The formula worked successfully without the OR statement: =COUNTIFS({Tier},…
-
CountIF formula with added criteria - NEED TO KNOW HOW TO ADD
I am trying to obtain counts with multiple criteria, I have tried looking at other solutions that do not work. This is my formula - =COUNTIFS({iPro Site Type}, $[Primary Column]@row, {iPro Region}, [Column2]$42) I need to understand how to add an additional criteria - I want it to return the count of all sites types per…
-
Countifs from one sheet with multiple categories
I need to complete a countifs formula for data in one sheet with multiple categories (screenshots of data needed). The columns that need to be counted have different drop-down options. I have to count active status defects the total (New, open, Assigned, Ready for Retest, Assigned to Vendor, Reopened) for each module. The…
-
Help with =JOIN(DISTINCT(COLLECT formula
@Paul Newcome I am really hoping you may be able to help with pulling together a formula! Looking to use a =JOIN(DISTINCT(COLLECT formula to generate a string of distinct codes for the Distrb SKU Nbr column where the SKU codes are separated by a comma. Based on criteria: no checkmark in the Available on TMC column,…
-
How to calculate the Average number of days based on a range with a date criteria?
I am trying to write a formula for calculating the average number of days in one range based on anything added to the sheet post July 1 2020. I have tried several combinations. I can get a simple AVERAGE of the range for the whole sheet but need to specify only average the data after July 1. Works and provides average for…
-
Adding up "revenue at risk" in a sheet summary based on RYG status
Please can anyone give me a hand on how I can add up "revenue at risk" in a sheet summary based on RYG status? I tried the following: =SUMIFS([Sales Price]:[Sales Price], Status:Status, "Red") The Sales price column is formatted for US Dollars and I would like the "revenue at risk" summary to be in USD as well. The status…