Bugs: SUMIFS does not find multiple users

J. Craig Williams

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

Tim Meeks

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

Daniel Stein

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

J. Craig Williams

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

Daniel Stein

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

J. Craig Williams

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

Paul Newcome

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.

Daniel Stein

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

Atul

Is there any update for above Bug ?

Even i wanted to use the SUMIF function to add duration for an user in a day.

Use case is:



User 1 spent time on different tasks

like - 

1 hour on Task 1

2 hours on Task 2

4 hours on Task 3 

(in three different rows)

but task 3 row has multiple contacts.



When i use SUMIF fucntion to add total time taken by USER 1 in a day then SUMIF function calculate only = 1+ 2 = 3 Hours 



Formula didnt consider the 4 hours value as this row has multiple contacts.

 

Daniel Stein

Hi Atul,

Thanks for the question. "Matching" functions like SUMIF, COUNTIF, IF, and VLOOKUP always look at the entire cell value, for consistency. If you want the function to do a "contains" search inside the cell, then you can use the FIND function with @cell, like this:

=SUMIF([Assigned To]:[Assigned To], FIND("Sally Smart", @cell) > 0, Duration:Duration)

While the above behavior is intended, Craig's scenario #2 (which results in #INVALID OPERATION in some circumstances) is indeed a bug, and we plan to ship a fix to it this week.

Best regards,

Daniel

J. Craig Williams

The problem with using FIND() for a substitute for what is needed is that 

Atul 

matches

Atul

AtulA

1Atul

andaverylongstringcontainingAtulandothercharacters

You can avoid some of those with a known [start-position] argument, but what is really needed is an EXACT MATCH.

The repercussions are that if searching for a specific US president, you can't search for "John", you need to search for "John Adams" and know that you won't find "John Quincy Adams"

To address the earlier question, Daniel, I think there will be as many use cases for that "if any of these people found, then COUNT THIS ROW" and that "if this specific person is found, add to his COUNT".

I don't think it likely that if both Jane and John are assigned to a row with a AMOUNT, it is desirable to add it to a sum twice, but I could be wrong about that.

I don't see how you will work your way out of the hole you have designed without having formulas that treat the cell as a list of individual assignments and a different formula (or additional arguments which historically Smartsheet has been loathe to add, from my viewpoint) for treating them as a single cell.

Craig

 

Daniel Stein

Thanks Craig, yes I agree it would not be advisable to do the FIND based on a first name only. First and last name should be included, which will work as long as there aren't two different people with the same first and last name. I updated the example above to include first & last.

I agree there are a number of different possible use cases for these formulas, including looking at whole vs. part of a cell, and looking at name vs. email address. The current approach is intended to be consistent across all of these functions as well as existing behavior (e.g. always look at the display value of the entire cell by default). We are open to adding additional functions and/or optional arguments in the future to help facilitate additional use cases that customers request.

Please keep the thoughtful feedback coming.

Daniel

J. Craig Williams

I am still hoping to use email address as that is unique across users.

Later, I'll ask for just the domain (@ronin-global.com) but that's a long way away for me.

Craig

Paul Newcome

I personally would love to see a new function that would give us the ability to specify whether to treat it as simply a cell with data or to treat it as a cell with x number of contacts or being able to specify an individual contact.

 

Something along the lines of just a cell reference in a formula returns the result as if it is simply a populated cell, but using CONTACTS([Column Name]1) would denote that we are looking at the contacts as individuals regardless of how many are in that cell.

COUNT all of the instances John Smith's specific contact shows up in the range [Contact Column]:[Contact Column].

SUM all of the hours Anne Smith worked across all tasks even if she is included in a multi-contact cell.

COUNT how many people are assigned to this specific task.

All of that fun mess.

J. Craig Williams

re: SUM all of the hours Anne Smith worked across all tasks even if she is included in a multi-contact cell.

This is a completely different (and horrible) problem - how to resolve [% Allocation] and [Duration] that won't be solved well given the current restraints.

Does [% Allocation] = 100% mean each individual is assigned 100%? What happens when another person is added to the assignment? (currently nothing)

And I don't want to think about those right now.

Craig

Paul Newcome

I don't really use Allocations in what I do. Hours worked would be a simple manual entry and therefore a basic value for me. When I said "hours worked" I was thinking along the lines of tasks that had ALREADY been completed and as a result wouldn't change.

 

I do see your point though, and I hadn't thought about it that way due to just plain old not using it that way.

 

I guess a better example would be something along the lines of AVERAGE the % Complete for all tasks Anne Smith is assigned to whether she be in a single or multi-contact field.

 

SPITBALL WARNING:

 

Maybe use the @mention type of format where you enter the @ and as you start typing the autofill list updates with all contacts fitting the text entered thus far.

@ = List of all contacts

@An = List of all contacts starting with An (Anne, Anthony, etc.)

@Anne = List of all contacts starting with Anne

So on and so forth.

 

=COUNTIFS(Status:Status, "Complete", Contact:Contact, CONTACT(@Anne Smith))

 

Counts how many times a task is marked complete where Anne Smith is in the Contact column or if some entries are Anne Smith and other entries are the email associated with Anne Smith such as annesmith@gmail.com. Would cover Multi-contact column types.

 

If only single contact type, the usual

 

=COUNTIFS(Status:Status, "Complete", Contact:Contact, "Anne Smith"))

 

would work (if her name is entered as "Anne Smith" of course).

 

Not sure if any of that makes sense or is even feasible, but there it is... Hahaha