7

Scenario #1

Multiple Resources assigned to Contact List type column (new release today)

This

=SUMIFS(Amount:Amount, [Assigned To]:[Assigned To], CheckName@row)

does not match the [Assigned To] when the Contact name in [CheckName] matches. (It does for single instances)

Scenario #2:

It is possible to possible to get into where this:

=SUMIFS(Amount:Amount, [Assigned To]:[Assigned To], [Assigned To]@row)

returns #INVALID OPERATION

This DOES NOT OCCUR if the "Allow multiple contacts per cell" is unchecked.

It MAY occur when it is checked, even if there are no multiple selections.

Scenario #3:

User names are done by Name not Email.

This will allow two Bob Smith's to cause confusion.

Craig

Comments

i just saw your post.sad .. i'm so busy it will probably be a couple of weeks before I can try out.. glad to know you are test driving...yes

Thanks Craig for your feedback, much appreciated. I looked into your scenarios and have a few follow-up comments/questions for you.

1. The SUMIFS function (and related functions) only matches against complete values of the cell, so it will not register a match if the target name is one of several names in the cell. You could accomplish a "contains" comparison by incorporating a FIND function to look for the name as a substring. (FIND will return 0 if the substring isn't found.) Does this explain what you are seeing?

2. Thanks for reporting this. I am reproing this as well, if both columns being compared are multi-contact columns, and agree this may be a bug. We will investigate.

3. Agree that using string comparisons will not differentiate between two people with the same name. I think a good way to do this deterministically would be to have functions that extract the email part of a contact, which is something we are considering as a future enhancement.

Thanks again,

Daniel

Daniel,

1. Yes, I figured that FIND might be a way around this. I would suggest instead treating the multiple selection as a list and use something like "is one of". It is very likely there will be duplicates of names. In fact, I think it was mentioned that email would used to avoid possible false positives.

2. If ANY column in the formula has the option selected it can result in the error.

3. Yes, email is the way to go. 

If a Sheet or KPI/Metric sheet contains any formula with a Contact List type in its arguments, I would advise against using this feature until these bugs are resolved.

Craig

In reply to by J. Craig Williams

Thanks Craig, we will investigate #2 and fix any issues there soon.

Regarding formulas treating contacts as email addresses instead of names: formulas have treated contacts as names for a long time, so we wouldn't be able to change this default behavior without affecting a lot of existing formulas. But I agree with you that allowing users to look at emails instead would be useful as an option.

Best regards,

Daniel

Daniel,

The name vs email issue is why I would have advocating for changing the Dropdown List first, as solving that would provide (imo) the framework to solve this one.

In addition, I have serious reservations about implementing this using a new column type. I think your team will come to regret that.

I hope I am wrong.

Craig

I've been experiencing issues with COUNTIFS as well. For example, I have 5 rows populated.

=COUNT(Contact:Contact)

This will return 5, but...

=COUNTIFS(Contact:Contact, NOT(ISBLANK(@cell)))

returns 0. If I switch the column reference to any column other than the contact column, I get 5 which would be the correct count.

Thanks Paul and Craig, we are actively working on the issue you noticed in COUNTIFS and SUMIFS and will release a fix as soon as we can.

On a related note, how would you ideally expect COUNT to work with multi-contact cells? Would you expect it to count every contact in the cell(s) (e.g. a cell containing 5 contacts would contribute 5 to the count)? Or would you expect it to count the number of cells, regardless of how many contacts are in the cells?

Best regards,

Daniel