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?!?

 

 

L_123

Yeah I had the same issue with conditional ranges in the collect formula. Re-arranged it worked find, but put it in the wrong order and no dice.

 

https://community.smartsheet.com/discussion/unexpected-behavior-collect-formula

L_123

Ha I forgot it was you who I was discussing that with I didn't update it but re-arranging the criteria fixed it in the end

Paul Newcome

Haha. I forgot about that little piece. I still have no idea why rearranging it worked. The sad part is I am using this to build a chart within a dashboard. No one want's to see zeros so usually I will do something along the lines of

 

=IF(Original Formula > 0, Original Formula)

 

This will display a number if there is one, but leave anything else a blank (no potential for negative numbers in these formulas).

 

I rearranged my formula and got it working. 

BUT...

When I put it in the above setup it threw the same #INCORRECT OPERATION error??

 

The second part of my IF statement is exactly the same as the first. Theoretically, if it is working as a stand-alone formula to produce a number, repeating the same exact formula within an IF statement as above should work.

 

I wonder if it is a glitch with having dates before certain data types regardless of where it is and in what context it is used if there are a few specific formulas in use.

 

I'm getting ready to reach out to SS Support for this one as it is rather frustrating. I spent WAY too much time trying to figure out what was wrong with it.

 

EDIT: The portions that have been stricken through were my own error... Ugh. I should probably just go home at this point...

L_123

Did you try switching this to a collect? It might make you trust it a little more, even if it doesn't help you understand what went wrong.

=count(collect({Implementation Rollup Type},{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)))

 

In my experience collect is pretty durable, so long as you don't have variable ranges.

Paul Newcome

I am fine with the COUNTIFS the way I have it working now as I have tested it with various entries across all of my different project types.

 

P.S.

Since that particular conversation you shared a link to, I now use the COLLECT function and @cell references A LOT. The flexibility and creativity it provides when writing formulas is just great and has enabled me to do some pretty crazy mess.

 

I once used a JOIN/COLLECT to pull the search criteria for a MATCH function within an INDEX function that was then used as the search criteria of another MATCH that was used to get my row number for yet another INDEX. I then had to use the same setup (different ranges though) to pull a column number. That was a mess. If I remember correctly, it looked a little something like this...

 

=INDEX({Range}, MATCH(INDEX({Range}, MATCH(JOIN(COLLECT({Range}, {Range}, @cell = x)), {Range}, 0)), {Range}, 0), MATCH(INDEX({Range}, MATCH(JOIN(COLLECT({Range}, {Range}, @cell = x)), {Range}, 0)), {Range}, 0))

Cat Werbeck

Hey Paul,

I have seen your formula wizardry around the community and wondering if you could help me get the correct argument down for the following use case:

Sum three columns if 2 conditions are present. I have tried SUMIFS and SUMIFS AND, moving commas, parans, and I am just not getting there- the formula is correct for one column, but I am definitely missing something in the sauce to add in the other two columns. I also tried to just repeat the formula so it sums each column- but I am not getting there. Any thoughts would be really really appreciated!!

Working correctly, anything I seem to try to touch from here is an incorrect argument, unparseable, etc. But basically I need it to do the same calculation {EST CapEx ex Int Labor1}, {EST CapEx ex Int Labor2}, {EST CapEx ex Int Labor3}

=SUMIFS({EST CapEx ex Int Labor}, {IT Strategic Priority}, $Label@row, {IT Project Mgr.}, Russ$1)

Keeping my fingers crossed you happen to see this :)

Thanks!

Cat

Paul Newcome

@Cat Werbeck Give something like this a try.

=SUMIFS({EST CapEx ex Int Labor1}, {IT Strategic Priority}, $Label@row, {IT Project Mgr.}, Russ$1) + SUMIFS({EST CapEx ex Int Labor2}, {IT Strategic Priority}, $Label@row, {IT Project Mgr.}, Russ$1) + SUMIFS({EST CapEx ex Int Labor3}, {IT Strategic Priority}, $Label@row, {IT Project Mgr.}, Russ$1)

Cat Werbeck

=SUMIFS(({EST CapEx ex Int Labor1}, {IT Strategic Priority}, $Label@row, {CAP PM}, Russ$1) + SUMIFS({Est. CapExInt Labor}, {IT Strategic Priority}, $Label@row, {CAP PM}, Russ$1) + SUMIFS({EST Expense Labor}, {IT Strategic Priority}, $Label@row, {CAP PM}, Russ$1))

is returning unparseable :(

Paul Newcome

You need to remove one of the opening parenthesis immediately after the first SUMIFS and one of the closing parenthesis from the end of the formula.

Take another look at the formula I provided. We are basically just adding some SUMIFS functions together.

=SUMIFS(.....) + SUMIFS(.....) + SUMIFS(.....)

Cat Werbeck

https://community.smartsheet.com/discussion/comment/266858#Comment_266858

Got it! TY!! Knew you were the wizard to find :)