Using SUMIFS formula with CONTAINS or HAS as Criterea

mtk5200

I'm trying to only add up budget line items from specific months and that do not contain the value "Total" in their account code. Here's the formula I have and just can't get right. After breaking it apart, I'm finding the CONTAINS part of the formula is what's incorrect.

=SUMIFS({Budget Detail $}, {Budget Detail Acct Code}, CONTAINS("Total", {Budget Detail Acct Code}), {Budget Detail Month}, 10)

Any help is greatly appreciated!

Paul Newcome

Since you already have the range established

=SUMIFS({Budget Detail $}, {Budget Detail Acct Code}, CONTAINS("Total", {Budget Detail Acct Code}), {Budget Detail Month}, 10)

The "range" in the CONTAINS function would be "@cell"

=SUMIFS({Budget Detail $}, {Budget Detail Acct Code}, CONTAINS("Total", @cell), {Budget Detail Month}, 10)

mtk5200

Thanks! This took care of it.

Paul Newcome

Happy to help. 👍️

Jonatas Rodrigues

Hi @Paul Newcome,

Can we use SUMIFS + CONTAINS + HAS? Meaning that it would sum all values in a range that contain any of the values in another cell.

Thank you,

Jonatas

Paul Newcome

@Jonatas Rodrigues It should be possible. Are you able to provide more details and possibly some screenshots for context?

Jonatas Rodrigues

@Paul Newcome Please see print below:

The result is 100, but as you can see the second code '286113' is on the first line with another code. I want the result to be 200... How can I implement CONTAINS here?