Multi-row Formula with multiple criteria

[Jholiday78]

Hi there,

I am half way to solving my issue but just can't get the formula correct.  Here's what I am trying to accomplish using these three fields to assign a 1 or 0 to the new field.

• If [customer] is equal to the [customer] in the next row but the [FindNearestRank] and [DistanceMiles] is not equal then 1 else 0.
• If [customer] is equal to the [customer] in the next row and the [FindNearestRank] and [DistanceMiles] is equal then 1 else 0.

Basically, I am trying to group the same [customer] if they are also grouped by [FindNearestRank] and [DistanceMiles] assign 1.  If they are in the same grouping, then I only want to count them once.

In the example, I have included a column for my [Desired New Field].

Thanks in advance for you assistance.

Jessica

[IraWatt · Accepted solution]

Hey @Jholiday78,

I think all you needed was to remove the [Customer]!= [Row-1:Customer] check. I have this as the formula:

IF [FindNearestRank] != [Row-1:FindNearestRank]
AND [DistanceMiles] != [Row-1:DistanceMiles]
then 1 else 0 ENDIF

Any questions or issues please ask :)
HTH!
Ira

[Jholiday78]

Thank you!  That worked.  I guess I didn't need to specify that info in the IF statement because I grouped it by [customer].