Formula Woes

Paul Newcome

My old formula:

=COUNTIFS({Implementation Rollup Type}, OR(FIND("new", LOWER(@cell)) > 0, FIND("exist", LOWER(@cell)) > 0), @{Implementation Rollup Work Start}, IFERROR(WEEKNUMBER(@cell), [1/7/19]$1) <= [1/7/19]$1, {Implementation Rollup Work Finish}, IFERROR(WEEKNUMBER(@cell), [1/7/19]$1) >= [1/7/19]$1, {Implementation Rollup Years}, OR(FIND($[Primary Column]$1, @cell) > 0, FIND("hold", LOWER(@cell)) > 0))

.

My current formula:

=COUNTIFS({Implementation Rollup Type}, OR(FIND("new", LOWER(@cell)) > 0, FIND("exist", LOWER(@cell)) > 0), @{Implementation Rollup Work Start}, IFERROR(@cell, DATE($[Primary Column]$1 + 1, 1, 1)) <= DATE([1/1/19]$12, [1/1/19]$10, [1/1/19]$11), {Implementation Rollup Work Finish}, IFERROR(@cell, DATE($[Primary Column]$1 - 1, 1, 1)) >= DATE([1/1/19]$12, [1/1/19]$10, [1/1/19]$11), {Implementation Rollup Years}, OR(FIND($[Primary Column]$1, @cell) > 0, FIND("hold", LOWER(@cell)) > 0))

.

All I changed was my Work Start and Work Finish Range criteria.

Initially I was taking the date from the cell and comparing its WEEKNUMBER to the cell in row 1 of my sheet which was simply a number 1 - 52.

I changed it to look at the actual date in the cell and compare it to a set of three cells on the sheet (month, day, year).

The basic layout of my sheet is this...

.

Primary Column       1/1/19          1/7/19          1/14/19

2019                            1                  2                   3

Implementations          f                   f                    f

Month                          1                   1                   1

Day                              1                   7                   14

Year                            2019           2019               2019

.

I am looking at my other sheet {Implementation Rollup} and trying to count how many rows have a start date less than or equal to the date in my column header and a finish date that is greater than or equal to the same.

Initially I was using row 1 and looking at the WEEKNUMBER(@cell) with this:

IFERROR(WEEKNUMBER(@cell), 0) <= [1/1/19]$1

which is saying if the week number is less than or equal to 1.

.

Now I am trying to say

IFERROR(@cell), DATE([Primary Column]$5 + 1, [1/1/19]$3, [1/1/19]$4)) <= DATE([1/1/19]$5, [1/1/19]$3, [1/1/19]$4)

which is saying if the date is less than or equal to the DATE(2019, 1, 1).

.

All numbers are in fact numbers. All columns are formatted appropriately. All I changed was from looking at WEEKNUMBER to looking at DATE. I have poured over this and combed through every single thing I can think of, but I just can't seem to figure out what is going on with it...

 

Getting the #INVALID OPERATION error which means I am not using a supported operator, or my operators are in the wrong order. All of my +'s and -'s were entered using the number pad on my keyboard, and all of my <= or >= references are in the correct order (= comes AFTER the < or >).

 

I'm lost on this one...

Paul Newcome

I was able to find a working solution, but I have no idea why it works and why the first formula wouldn't. Any insight would be great.

.

My old formula that was throwing an error:

=COUNTIFS({Implementation Rollup Type}, OR(FIND("new", LOWER(@cell)) > 0, FIND("exist", LOWER(@cell)) > 0), @{Implementation Rollup Work Start}, IFERROR(@cell, DATE($[Primary Column]$1 + 1, 1, 1)) <= DATE([1/1/19]$12, [1/1/19]$10, [1/1/19]$11), {Implementation Rollup Work Finish}, IFERROR(@cell, DATE($[Primary Column]$1 - 1, 1, 1)) >= DATE([1/1/19]$12, [1/1/19]$10, [1/1/19]$11), {Implementation Rollup Years}, OR(FIND($[Primary Column]$1, @cell) > 0, FIND("hold", LOWER(@cell)) > 0))

.

The formula that is working:

=COUNTIFS({Implementation Rollup Type}, OR(FIND("new", LOWER(@cell)) > 0, FIND("exist", LOWER(@cell)) > 0), {Implementation Rollup Years}, OR(FIND($[Primary Column]$1, @cell) > 0, FIND("hold", LOWER(@cell)) > 0), @{Implementation Rollup Work Start}, IFERROR(@cell, DATE($[Primary Column]$1 + 1, 1, 1)) <= DATE([1/1/19]$12, [1/1/19]$10, [1/1/19]$11), {Implementation Rollup Work Finish}, IFERROR(@cell, DATE($[Primary Column]$1 - 1, 1, 1)) >= DATE([1/1/19]$12, [1/1/19]$10, [1/1/19]$11))

.

For whatever reason... Putting the date ranges/criteria last seems to have worked. Why is it that it works this way and not the other even when it is the SAME EXACT TEXT?!?