Formula for "HAS" and "CONTAINS"

Mel_Barnes

I am trying to calculate how many hours each person is allocated to each project. We have list of all of the tasks pulled from each plan and I am trying to write a formula to calculate total hours if the project ID is in the sheet name, if the resource assigned is the person @row and if the start date is today + 7 days.

It is returning #INVALID OPERATION

Gillian C

Hi @Mel_Barnes

A couple of questions:

When the Project ID appears in the column you are searching {Resource: All tasks Sheet name}, is it only the Project ID that will be in the cell or is it part of a string of text?

When the Resource Name appears in the column you are searching {Resource: All tasks Assigned to}, is it only one person that is assigned or can it be multiple resources

Mel_Barnes

Hi @Gillian C

Project ID is part of a string of text (ie. "Plan SM-123 Project Name"), the assigned to could be either one person or multiple resources.

Many thanks
Mel

Gillian C

Hi @Mel_Barnes

Try adding the bold text to your formula?

=SUMIFS({Resource: All tasks Hours}, {Resource: All tasks Sheet name},CONTAINS([Project ID]@row, @cell), {Resource: All tasks Assigned to}, HAS(@cell,[Resource Name]@row),{Resource: All tasks Start date}, TODAY() + 7)

Mel_Barnes

@Gillian C

It doesn't allow me to bolden specific text in the formula. If I highlight the text and click "bold", it boldens the cell text and still returns #INVALID OPERATION