-
Problems using @cell with dates when range contains blank cells
Hi, I've been trying to use this formula: COUNTIF(Due Date, MONTH(@cell) = 8, but it returns an error INVALID DATA TYPE If I limit the data range to exclude the blank rows than it has no problems. But this is not pratical because later when I more tasks, I have to adjust the formula. Interestingly SUMIF does not have this…
-
Return sum of one column if today's date is between A and B, etc
Hi guys, I can get the following formula to work without issue: =IF(TODAY() = DATE(2019, 8, 9), SUM([2019 Aug]:[2019 Aug])) What I can't do is update the logical expression to be if today's date is between Aug 1 and Aug 31. Essentially I want a self-updating formula that will always show me the sum of the appropriate…
-
Link count and Ranges - Does each column in a range count as a link?
Hi Folks, After reading up on the issue of cross sheet link limits, I would like to confirm what 'counts'. If a range is defined as having 10 columns does that count at 1 link or 10? If a range has 20 columns is that 20 links or one? And finally if that same range is used for several VLOOKUP functions in a destination…
-
List of all ranges defined for a sheet
Hi Folks, We have been using a lot of cross sheet links and need to clean things up. Is there a way to see all the named ranges used to access data in a sheet? In other words, show all the ranges and thus the sheets, that are using data from the current sheet? Thanks!
-
index/match help
Please refer to the attached image. I would like to create a formula to display just the "Preliminary" cost for "Atlanta - Cumming" on a separate sheet using the index/match formula but keep getting an error. Can anyone help with this? I am open to using another function as well.
-
Sumifs with 2 conditions from same column
I am having trouble creating a formula with 2 conditions. I currently have formulas in a Spend summary sheet that only have one condition but I need to add a 2nd condition. Here is what I have for 1 condition: =SUMIFS({Team DD - Budget - FY19-FY20- Amount}, {Team DD - Budget - FY19-FY20- Requester Team}, "Computing", {Team…
-
Using COUNTIFS with multiple conditions
Trying get a count of issues in a catergory sheet that is referenced from a main sheet and then add another condition to count the categories of only the ones where the site is checked. The first COUNTIFS works, but not the 2nd one like below . =COUNTIFS({Day 1 Support Log Range 1}, =[Issue Category]1, {Day 1 Support Log…
-
Make Cell Blank if all Columns are "N"
Hi there, Wondering how to amend this formula so that if all the cells are "N", it will return a blank in column "Report Status" =IF(OR([PAR Items]16 = "Y", Proxies16 = "Y", Commissions16 = "Y", Constraints16 = "Y", [APX Reports]16 = "Y", [MRFP/SMA]16 = "Y", Commentary16 = "Y", Attachment16 = "Y", (COUNTIF([PAR…
-
IF Formulas for Status Columns
Hello, I'm looking for assistance in writing an IF formula to change a cell's value based on the value of other cells in the same column. Specifically, the values will be symbols. If all symbols are green, I'd like the first row to show as green, if any are yellow, then the top row should be yellow, and lastly if any are…
-
SumIFs formula #INVALID DATA TYPE error
Getting the #INVALID DATA TYPE error with this formula: =SUMIFS({Forecasts (SS) Hours}, {Forecasts (SS) Consultant}, $[Consultant Name]23, {Forecasts (SS) Start}, WEEKNUMBER(@cell) = 48, {Forecasts (SS) Cancelled}, 0, {Forecasts (SS) Start}, YEAR(@cell) = 2019) What's frustrating is if I change the ${Consultant Name]23 to…