How can I sum values for a specified column when part of a text is included in other column cells?

Karol

Dear community

I'm stuck with a small problem. I have column with values (hours worked) and an column which contains different names of employees. I would like to sum the values (hours) for an employee. My problem is, that for a value there can be 2-3 names in the cell of the name column (employees worked together on the same matter).

=SUMIFS([hours.]:[hours.], employee:employee, "*Karol*")

didn't work out.

How can I specify/search text parts in a cell if a cell contains more than one name?

How can I specify that hours are summed up only for a specific month (January, February ..)? A date column is available.

Thank you very much.

Best regards

Karol

Karol

Dear Purnima

Thank you very much and also a happy New Year to you.

In the meanwhile I found a possibility and the following worked for me:

row wise:

=IF(FIND("Karol", Employee@row) > 0, [Hours worked]@row, 0)

then sum up.

direct sum, e.g. for december:

=SUMIFS([Hours worked]:[Hours worked], Employee:Employee, FIND("Karol", @cell) > 0, Date:Date, MONTH(@cell) = 12)

Best regards

Karol

Purnima Gore

Hi @Karol

I wasn't able to do it using the contact name but if you use the multi select drop down there is a work around.

In the names use multi select drop down for names (I have used intials for ease)

Then use the contains formula to determine if the person exists in that row. You could of course change the columns to names

That then gives you a true and false statement

You can then use the summary fields to calculate the numbers

The best approach for this though would be to provide a form for logging time against a matter so that under their name, they can use the drop down to select the matter. If you have data shuttle then you can create a sheet that has all the matters and then copy it into the dropdown for when the employee logs their time. If you don't have that, then do what I do which is copy the row and manually add that to the drop down list.

That will work much better for reporting as well as you will be able to group by name if you want to check this.

Happy New Year.

Karol

Dear Purnima

Thank you very much and also a happy New Year to you.

In the meanwhile I found a possibility and the following worked for me:

row wise:

=IF(FIND("Karol", Employee@row) > 0, [Hours worked]@row, 0)

then sum up.

direct sum, e.g. for december:

=SUMIFS([Hours worked]:[Hours worked], Employee:Employee, FIND("Karol", @cell) > 0, Date:Date, MONTH(@cell) = 12)

Best regards

Karol