-
COUNTIFS #INVALID REF appear after a period of time
Hello, Able to get the countifs to total up some information in a different sheet. But then after a period of time, some of the cells using the countifs display #INVALID REF when it worked when I created my sheet screen shot shows a portion of my sheet, all cells use countifs and all cells did count what I wanted it too.…
-
Unique list of values w/ VLOOKUP or INDEX/MATCH not working
I am trying to use the aforementioned functions to crate a list of unique values by referencing a separate sheet. The source sheet contains a column of date values and a column of checkboxes which indicate unique dates. using either functions indicates #NO MATCHES which is incorrect. Please let me know how to make this…
-
Calculating Count of Projects by PM by Month
Hi, Trying to create formula to calculate How many projects start in a certain month by each Project Manager - My excel formulas are weak Master sheet has columns: Project Manager is Contact List (does this have to be a Text/Number Column?) Project Start Date - is Date type Column =COUNTIFS({Keller Project Summary - MASTER…
-
INDEX(MATCH) returns #INVALID COLUMN VALUE
I have two sheets with a common "Name" column between them. I would like for a checkbox in the second sheet to reflect in the first sheet, factoring in that not all of the names will be used in the second sheet. I would normally use INDEX(MATCH) for this purpose. MATCH() is able to return the row number for the Name where…
-
Formula for finding the first and last days of a year
I need to be able to calculate the first and last days of the year based upon the current date. Can someone help me out?
-
IF formula issue
The formula I've created isn't working correctly and I can't figure out what I'm doing wrong. =IF([New NCN Score Total]120 = 15, IF([New NCN Score Total]120 <= 14, IF([New NCN Score Total]120 <= 9, IF([New NCN Score Total]120 <= 6, "Very Good"), "Good"), "Poor"), "Very Poor") Scores 15 = Very Good 10 to 14 = Good 7 to 9 =…
-
Formula for calculating installs for a given quarter. Q1, Q2, etc..
i have this formula for calculating installs YTD for a given property. =SUMIFS({Mira Santi Install QTY}, {Mira Santi Install Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())) Not sure how to take this formula to use it to calculate installs for a given quarter. Do i just put the date range for the quarter? i.e Jan 1,…
-
Is there a shorter way of writing this IF statement?
Here's the working statement: =IF(AND([Account Owner]191 = "JK", [SALES REP]191 = "JK", OR([BILLED BY]191 = "Merit Medical", [BILLED BY]191 = "Don Joy"), STATUS191 = "Paid"), CHARGES191 * 0.14, IF(AND([Account Owner]191 = "JK", [SALES REP]191 = "JK", OR([BILLED BY]191 = "Cerapedics", [BILLED BY]191 = "Surgentec", [BILLED…
-
Sum ifs 3 criteria
I'm looking for the sumifs= Status column is "green", The Trans Date column in January 2018 the reversed column is unchecked. So far I only have the first part of the formula working. I cannot figure out how to include the rest =SUMIF(@{Collections Range 3}, @cell = "Green", {Collections Range 1}) This information will be…
-
If forumula
I trying to write a formula if (Days)<=30 will display an Amount in column 0-30