-
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…
-
PTO ACCURAL TO REFRESH ON JAN 1ST
Has anyone figured out how to refresh the pto accrual on Jan 1st? This is my formula that works fine in this current year: =IF([EMPLOYMENT TYPE]@row = "FULL-TIME", [WEEKS WORKED FROM START MONTH AND DAY TO DATE]@row * 1.66, 0)
-
Help with IF/ Then Formula Based on Number of Rows
Hey Guys, I want to create a formula that will return a statement when a number of certain number of rows is reached. I have a unique cell that has a number let's say 10, and I created a helper column (Row ID) that will simply designate each row by a number. I want to create an automatic statement in another cell that will…
-
What Function To Use With Multiple Values
I am trying to make a sheet that allows me to select an item (product) from a dropdown menu and then auto-populates a unit price for that specific product. I have 16 different products listed and different prices for each product. I have tried the =IF(CONTAINS() function and =IF(OR() function. The =IF(OR() function worked…
