CONTAINS Multiple Columns

Michelle Maas

I have a sheet that tracks project works by [acronym] or [client name] in Primary Column, with additional columns that track what time assigned people spend working on each project. I want to SUM all project time for each client in the Sheet Summary with a formula that picks up those specific requirements, for example:

=IF(OR(CONTAINS([Primary Column]:[Primary Column], "Client Name", "Acronym", @cell), SUM([Employee]:[Employee])))

=SUMIF([Employee]:[Employee], CONTAINS([Primary Column]:[Primary Column], "Client Name", "Acronym", @cell)))

I'm getting errors i.e. #incorrect argument set. Appreciate your help on getting the formula functioning. 😊

Jeff Reisman

@Michelle Maas You are almost there. The CONTAINS syntax gets a bit tricky. Try this:

=SUMIFS({Metrics Project Time Total Cost}, {Metrics Project Primary Column}, OR(CONTAINS("XXX", @cell), CONTAINS("Client Name", @cell)))

Jeff Reisman

@Michelle Maas Here's the explanation for this error. In your case, since your ranges are two entire columns, we can surmise that you are missing or have extra arguments in your formula - basically a syntax error.

#INCORRECT ARGUMENT SET

Cause

This error is presented under the following circumstances:

For functions that take two ranges: The range sizes don’t match for the function.

The function is missing an argument.

There is an extra function in the argument.

Resolution

Correct the range size or arguments, adding or removing arguments in the formula.

You want your SUMIF to work if the Primary Column equals either of these two text values - "Acronym" or "Client Name" - right? Or do you want it to work for a specific acronym or client name?

And you want it to add up the project hours each employee spends with each client? What column do the project hours get recorded in?

Where do you want to put these sum totals? On the sheet where all the data is recorded, or on a metrics sheet? How are you organizing it - a row for each employee for each project? (like Jim works on 5 client projects so he gets 5 rows, Sally works on 7 client projects so she gets 7 rows...)? Or a row for each client project and columns for each employee?

Michelle Maas

@Jeff Reisman Hi Jeff, thanks for getting back to me. Above is the table I am collecting the metrics in from referenced time reports. The redacted columns are allocated to each employee and project hours.

We record the project hours referenced against the primary column. Larger projects have a number and name system and small works has an index number with an acronym for the client, and most clients have different types of projects. What I want to calculate is the total hours of project works per client.

Jeff Reisman

@Michelle Maas

In the metrics sheet above, add a column at the far right, Total Hours, that sums all the project hours for the row. Just type =SUM( and then select all the employee columns for the row.

The way I would do this would be to use a second metrics sheet. For your primary column, list all your client identifiers. Then create a Total Project Hours column. Use SUMIFS to collect the total project hours per client from your first metrics sheet:

=SUMIFS({Time Reports Metrics Sheet Total Hours col range}, {Time Reports Metrics Sheet Primary Column range}, [Primary Column]@row)

In English: Give me the sum of values in the first metrics sheet's Total Hours column where the Primary Column value on the first metrics sheet equals the Primary Column value from this row on the second metrics sheet.

Michelle Maas

@Jeff Reisman Thanks for getting back to me. I'm almost there with the formula. I have a separate metric sheet that is pulling data from this metrics sheet where I have added the sum total column for the hours. The formula works if I just reference the client name:

=SUM(COLLECT({Metrics Project Time Total Cost}, {Metrics Project Primary Column}, [Primary Column]@row)

I need the formula to also recognise and pick up the additional hours that are referenced against a number/acronym system i.e. 000123 XXX* and add them to the overall total.

*= three letter acronym for client

This is where I've got to with the additional part of the formula but get #INCORRECT ARGUMENT when I add the CONTAINS part to reference the acronym as well as the client name:

=SUM(COLLECT({Metrics Project Time Total Cost}, {Metrics Project Primary Column}, [Primary Column]@row, {Metrics Project Primary Column}, OR(CONTAINS(@cell = "COL"))))

Michelle Maas

Bit closer but getting a '0' value:

=SUMIFS({Metrics Project Time Total Cost}, {Metrics Project Primary Column}, OR(CONTAINS(@cell = "XXX", @cell = "Client Name"))

Jeff Reisman

@Michelle Maas You are almost there. The CONTAINS syntax gets a bit tricky. Try this:

=SUMIFS({Metrics Project Time Total Cost}, {Metrics Project Primary Column}, OR(CONTAINS("XXX", @cell), CONTAINS("Client Name", @cell)))

Michelle Maas

Fantastic that works great, thanks @Jeff Reisman

Seth Morth

Thank you @Jeff Reisman! This totally helped me solve a formula issue I was having today!!!

https://community.smartsheet.com/discussion/comment/330056#Comment_330056

Jeff Reisman

https://community.smartsheet.com/discussion/comment/338473#Comment_338473

😀👍️