SUMIFS + CONTAINS Formula help.

Chandler Koury

Hi there!

I am trying to figure out how many projects start in the current month for a designated department.

This formula works: =COUNTIFS(Department:Department, "Marketing", Start:Start, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))

However, I tried to apply it to a new sheet where the department is a multiple drop down and more than one department can be selected per project. It's only picking up the cells that JUST have the "Marketing" department.

I tried to use CONTAINS and came up with this, but its not working:

=COUNTIFS(Department:Department, "Marketing", (CONTAINS("Marketing")) Start:Start, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))

Any suggestions?

Thank you!

L_123

=COUNTIFS(Department:Department, CONTAINS("Marketing",@cell) Start:Start, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))

Give that a try.

Paul Newcome

@L@123

Missed a comma between the CONTAINS and the next range.

=COUNTIFS(Department:Department, CONTAINS("Marketing", @cell), Start:Start, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))

L_123

@Paul Newcome Whoops. Good callout thanks

Paul Newcome

Haha. No worries. Sometimes I think commas are more problematic than parenthesis.

Tony Hope

Hi - I'm struggling to get something similar to work so looking for advice.

I would like to total the number of days allocated to each resource in my project plan to see what overhead I have. The "Assigned To" column has multiple contacts...

SUMIF(ASSIGNED:ASSIGNED, CONTACT NAME,DURATION:DURATION) works if there is only one contact name in the column.

Tried SUMIF(ASSIGNED:ASSIGNED, CONTAINS(CONTACT NAME,@CELL),DURATION:DURATION) but it just returns 0.

Genevieve P.

Hi @Tony Hope

Try this:

SUMIF(ASSIGNED:ASSIGNED, HAS(@cell, "Contact Name"), DURATION:DURATION)

The HAS function was specifically designed for multi-select lists to see if the cell HAS a certain value.

Keep in mind that the "Contact Name" will need to be exactly how your contact is spelled. If this doesn't work, it would be helpful to see a screen capture of your sheet, and an exact copy/paste of how you're inputting the formula.

Thanks!

Genevieve

Megan Harry

@Paul Newcome

Hello,

I am trying to do sum if collect but with more than one set of criteria.

=SUMIF(Month:Month, CONTAINS("January", @cell, "February", @cell, "March" @cell), [Number of Visitors]:[Number of Visitors])

I was able to do it with just January, but not the other two months.

Any suggestions?

Thank you!

L_123

=SUMIF(Month:Month, or(CONTAINS("January", @cell),contains("February", @cell),contains("March" @cell)), [Number of Visitors]:[Number of Visitors])

Megan Harry

@L@123

Thank you! I got it to work:)

Paul Newcome

@L@123 Thanks for filling in. My workload has increased to an absurd amount, and I haven't had tie to hang out here as much as I used to.

L_123

@Paul Newcome

Yeah I've taken on a new job so I haven't been on much either. Been on here less than once/week. Only checked this one cause I got the alert.

Paul Newcome

@L@123 That's pretty much where I am now. Only checking alerts and not able to help with the more complicated solutions. If it is going to take more than a few minutes I have to let it sit for a while until I can work on it more.