CountIfs Formula

Joey135

I would like the below formula to look at the Associate column (external sheet) and find Ashley. If it is Ashely then I want it to count if there is an issue logged in a four column range (same external sheet). It is comparing the issue in the range to the cell in my primary sheet.

=COUNTIFS({Associate}, "Ashley", {Issue Range}, CONTAINS([Primary Column]@row, @cell))

Paul Newcome

All you need to do is remove some of the closing parenthesis from the very end of the formula and use them to close out each of the individual COUNTIFS.

Mark.poole

Hey @Joey135

I may be wrong but you may have been over thinking what's needed to accomplish what your wanting.

Try this.

=Countifs({Associate},"Ashley",{Issue Range},[Primary Column]@row)

Joey135

@Mark.poole I had tried a version of that but got an unparseable error. Any other suggestions?

=COUNTIFS({Associate}, "Ashley", {Issue Range}, [Primary Column]@row, @cell))

Mark.poole

@Joey135

Could I perhaps have an example of the sheet your working with so I could try building you a solution?

Mark.poole

Does the count if work with out looking for Ashley?

Joey135

This works: =COUNTIF({Issue Range}, CONTAINS([Primary Column]@row, @cell))

But if I add any distinguishing factors, it does not.

Joey135

Screen Shot

Paul Newcome

@Joey135 You forgot the CONTAINS function in the second formula.

Joey135

@Paul Newcome The formula gives me and Incorrect Argument error. I feel like this should be an easy one, but I guess not. Do you think is make any difference that the Associate column I am referring to is a contact list. Maybe I should try the email address.

=COUNTIFS({Associate}, "Ashley", {Issue Range}, CONTAINS([Primary Column]@row, @cell))

Joey135

Just to reiterate:

Working formula: =COUNTIF({Issues Range 1-4}, CONTAINS([Primary Column]3, @cell))

Not Working formula: =COUNTIFS({Associate}, "Ashley", {Issues Range 1-4}, CONTAINS([Primary Column]1, @cell))

I seem to have stumped the community. I really appreciate this forum, it has never failed to get me the answer I need. I am in a holding period until I get this to work so if there is anyone out there that has a suggestion, I am open and waiting.

Paul Newcome

How many columns does {Issues Range 1-4} cover?

Joey135

Four columns. Maybe I need to break them out some how.

Paul Newcome

That's where the new error is coming from. All ranges inside of a function must be of the same size and shape. If your contact range is a single column, then all other ranges in the COUNTIFS must be single columns. You will need to write out 4 separate COUNTIFS (one for each of the columns) and then add them together.

=COUNTIFS(………………..) + COUNTIFS(………………..) + COUNTIFS(……………..) + COUNTIFS(……………………….)

Joey135

This is what I understood:

=COUNTIFS({Associate}, "Ashley", COUNTIFS({Issue #1} + COUNTIFS({Issue #2} + COUNTIFS({Issue #3} + COUNTIFS({Issue #4}, CONTAINS([Primary Column]@row, @cell))))))

Incorrect Argument Set

Paul Newcome

Your parenthesis are off. Your syntax for adding them together would be the same as in my example. You just need to fill in the middle of each with your range/criteria sets.

Joey135

Do you mean the below? This returns a 0 when I know there are issues to count.

=COUNTIFS({Associate}, "Ashley", {Issue #1}, CONTAINS([Primary Column]@row, @cell) + COUNTIFS({Associate}, "Ashley", {Issue #2}, CONTAINS([Primary Column]@row, @cell) + COUNTIFS({Associate}, "Ashley", {Issue #3}, CONTAINS([Primary Column]@row, @cell) + COUNTIFS({Associate}, "Ashley", {Issue #4}, CONTAINS([Primary Column]@row, @cell)))))