SUMIF formula help

System

This discussion was created from comments split from: Need support using wildcard & sumif function.

Jacqué Whitlock

Hi

Hoping someone can help!

As we don't have the Resource Management add on I'm trying to calculate the number of days assigned to each person in the project plan, however some "Assigned To" cells contains multiple names.

I have tried to use the above formula provide by Parul Mishra

=SUMIF({PROJECT PLAN: CERIDIAN DAYFORCE Range 2}, CONTAINS("Jack Jones", @cell), {PROJECT PLAN: CERIDIAN DAYFORCE Range 3})

(Range 2 is "Assigned To" column, Range 3 is "Duration" column)

But the formula is either returning 0 or 1, even though I know that Jack Jones has multiple days.

Any help would be much appreciated.

Many thanks

JW

(PS not sure what the @cell part of the CONTAINS formula is referring to)

Kelly Moore

Hey @Jacqué Whitlock

To answer the @cell question. This tells the function 'CONTAINS' to look at each cell, one by one, in your range {PROJECT PLAN: CERIDIAN DAYFORCE Range 2} and see if it contains "Jack Jones".

When using a multiselect column, you have three functions that can help see the individual responses within the cell. These are CONTAINS, HAS and FIND.

Try this

=SUMIF({PROJECT PLAN: CERIDIAN DAYFORCE Range 2}, HAS(@cell,"Jack Jones"), {PROJECT PLAN: CERIDIAN DAYFORCE Range 3})

Does this work for you?

Kelly

Jacqué Whitlock

That work's perfectly thank you Kelly, very much appreciated 😊