Unparseable error - multiple functions

james_c

Hi,

I'm trying to complete the below formula to identify the most recent royalty review date in a series of reviews but can't quite seem to nail it.

What i'm trying to achieve is if:

the column named "review completed" has a number of reviews marked "yes", then take the most recent date in the adjacent "review due date" column, and add a set number of days ("months" column) based on matching the "frequency of review" cell to the lookup table "payment / review periods". Thus hopefully returning the most recent review + the appropriate number of days.

=IF(COUNTIF($[Review Completed (Y/N)]$17:$[Review Completed (Y/N)]$18,"Yes"),(INDEX($[Review Due Date]$17:$[Review Due Date]$18,MATCH("Yes",$[Review Completed (Y/N)]$17:$[Review Completed (Y/N)]$18+0)),VLOOKUP($[Frequency of review]16,$[Payment / Review Periods]$1:$Months$9,2,0)),MIN($[Review due date]$17:$[Review due date]$18))

Many thanks

Paul Newcome

Could you provide some screenshots with sample data of how it is supposed to look?

L_123

I can see a couple issues with your formula, though I'm not entirely sure what you are attempting to do. The first, and easiest to fix error is your if(countif()) statement.

=if(countif()) will work if there is only one result that is found true. If you are expecting multiple potential values you need a criteria or you will pop an error

=if(countif()>0

=IF(COUNTIF($[Review Completed (Y/N)]$17:$[Review Completed (Y/N)]$18,"Yes")>0

Next your match statement will match the first true value, not the largest or most recent value. Which might be fine if you have new rows added to the top, but in my experience sheets rarely work that way. instead of index match in this case you can use max(collect()) to get the value you want.

=max(collect($[Review Due Date]$17:$[Review Due Date]$18,$[Review Completed (Y/N)]$17:$[Review Completed (Y/N)]$18,"Yes"))

Without seeing the table I have no clue what you are doing with the Vlookup, but since it is separated as a false criteria of the if statement i'll just guess that you have it correct and tack it on.

=IF(COUNTIF($[Review Completed (Y/N)]$17:$[Review Completed (Y/N)]$18,"Yes")>0,max(collect($[Review Due Date]$17:$[Review Due Date]$18,$[Review Completed (Y/N)]$17:$[Review Completed (Y/N)]$18,"Yes")),VLOOKUP($[Frequency of review]16,$[Payment / Review Periods]$1:$Months$9,2,0)),MIN($[Review due date]$17:$[Review due date]$18))

Like I said, i'm not sure what you are trying to do with this, but hopefully the above helps.

here is a resource on the collect formula as it is probably the most difficult thing to understand out of what I have posted

 

https://help.smartsheet.com/function/collect

 

Paul Newcome

L@123,

 

You are right in that COLLECT can be a bit tough to wrap your head around at first. I personally have found that it makes the most sense to people if I explain it as an IF(S).

 

SUM has SUMIF(S)

COUNT has COUNTIF(S)

but what if you need the IF(S) portion for a function that doesn't have one like AVG or JOIN?

 

That's where COLLECT comes in.

 

If you want to AVG or JOIN a range IF it meets certain criteria, just start it out with a COLLECT.

 

AVG(COLLECT(..........)) is the same as AVGIF(S)

JOIN(COLLECT(.......)) is the same as JOINIF(S)

 

so on and so forth.

 

If they need an example, I will show them that SUM(COLLECT(.......)) works exactly the same as SUMIFS. Same with COUNT(COLLECT(.......)) although the syntax is a little different from the COUNTIFS. It still works the same.

L_123

I've always thought of it as a way to return an array. Once you understand that a collect returns multiple values filtered by your criteria out of the range you select, in the order that they meet the criteria, using it becomes pretty natural. Though I have had some experience with a few programming languages.

It helps to understand that formulas in smartsheet operate like a book. Start at the top left, read the row, then work down until the end is reached.

james_c

Thanks for repsonding Paul.

A couple of screenshots attached - one showing how it looks in excel when working, and the other in smartsheet. Hope that helps.

james_c

L@123,

Many thanks for the response. I'll have a look at this later today and see if I can figure it out. 

With regards the lookup table I have attached a screenshot below.

The general purpose of the formula is to identify when the next royalty review is due based on the review history. So if a review was missed 3 years ago, it will still show that that review is due. If up to date, it will show the next review due date in the future.

Thanks

Paul Newcome

The only experience I have with programming languages is looking at them and going "HUH??". I just recently learned the very basics of what an API is used for. Haha.

 

Understanding that the formulas operate like a book is most certainly helpful though. If I ever have something returning an unexpected value, the first thing I do is look for the location of the value returned to see where my criteria may need tweaked.