SUMIF and CONTAINS

Mike Willacy

Hi, I'm hoping someone can help me. I have this formula, which works partially:

=SUMIFS([Hours]:[Hours], SIM:SIM, "Laura")

It sums up all the hours in the 'Hours' column when the staff member in the 'SIM' column is "Laura"

However there might be multiple staff members in the same cell in the SIM column, and when that happens, the formula ignores it.

I need the formula to recognise if 'Laura' is in the cell, regardless of whether there are any other members of staff in the same cell. I've tried the CONTAINS function as well as the HAS function but I'm either getting errors or the return in 0 when it shouldn't be.

Any help?

KPH

Does this work for you?

=SUMIFS(Hours:Hours, SIM:SIM, CONTAINS("Laura", @cell))

Mike Willacy

Thanks for your reply. It returns 0, which isn't right.

KPH

Are you putting the formula in the same sheet as the data?

Can you share a screen shot of the Hours and SIM columns in your sheet?

Mike Willacy

I'm not sure what's happening, as when I create a new sheet and input the figures and all data manually, my original formula works. However in my main Smartsheet the figures are generated through a formula, so I'm wondering whether this is the issue.

Saying that, I created a new column in my main Smartsheet, copied and pasted the figures in and used the SUM function, but it still gave the wrong amount.

The numbers I want to add up are below. In smartsheet it comes to 7.88 rounded, in excel it comes to 14.125.

0.5

0.5

0.33333

0.33333

0.33333

0.5

0

0.33333

1.5

0.16667

0.16667

1.5

0.16667

0.5

0.33333

0.5

0

0

0

0

0

0.25

0

0.75

0.125

0

0.33333

1

0

1

0.16667

0.5

1

0.16667

0.16667

0

1

KPH

Hi

I think there is something else going on, not an issue with the SUMIFS formula.

Looking at those numbers and the result you get, makes me think it could be something to do with the Column Type. Is your column that contains the numbers to sum a Text/Number column? If so, the total of the numbers you posted should match with the total in Excel. But if you have a different column type you could get funny results. "Duration" should work fine. However,...

If the column type is changed from "Date" and then to "Duration" then the 1s and 0s remain as dates but the decimals convert into parts of days and only those are included in the SUM, essentially removing any 1s from your sum. That doesn't give exactly 7.88 but is close and might be a hint in the right direction.

If that isn't it, could you share the formula you use to create the numbers to SUM? There could be something there. Maybe a mix of numeric and text?