Help with COUNTIF, CONTAINS

Ashley McAdoo

I am trying to do a sheet summary that counts how many of a particular type of document are listed in a "Document" column. I got the Work Order (all listed as "Work Order") function to work, but I can't get a count of just "Change Order" as they are all listed as "Change Order 1", "Change Order 2", etc. Here is my formula - =COUNTIF(Document:Document, CONTAINS("Change" (@cell)))

Where am I going wrong?

Thanks,

Ashley

Paul Newcome

Just need a little tweak to the CONTAINS function:

=COUNTIFS(Document:Document, CONTAINS("Change", @cell))

Paul Newcome

You can use a COUNTIFS for a single range/criteria set, but you cannot use a COUNTIF for multiple range/criteria sets. I am in the habit of always using COUNTIFS so it is one less thing for me to forget if I do end up needing to add to it.

With a single range/criteria set it is just personal preference. In this instance I simply used it out of habit rather than necessity.

Paul Newcome

Just need a little tweak to the CONTAINS function:

=COUNTIFS(Document:Document, CONTAINS("Change", @cell))

Ashley McAdoo

That did the trick, @Paul Newcome. Thank you so much! But why use COUNTIFS? I thought that was for counting things in two different columns?

Paul Newcome

You can use a COUNTIFS for a single range/criteria set, but you cannot use a COUNTIF for multiple range/criteria sets. I am in the habit of always using COUNTIFS so it is one less thing for me to forget if I do end up needing to add to it.

With a single range/criteria set it is just personal preference. In this instance I simply used it out of habit rather than necessity.

Ashley McAdoo

Good to know! Thanks again!

Paul Newcome

Happy to help. 👍️

Ashley McAdoo

Hey @Paul Newcome, I have another question for you. I need to return a count in the same sheet summary of contracts expiring in the next 30 days. My formula was =COUNTIF([Expiration Date]:[Expiration Date], <TODAY(30)), but I realized that is capturing already expired contracts as well. Any thoughts?

Paul Newcome

Try this:

=COUNTIFS([Expiration Date]:[Expiration Date], AND(@cell < TODAY(30), @cell >= TODAY()))

Note: Still don't NEED to use the COUNTIFS because it is technically only one range/criteria set (criteria is lumped into one set using the AND function). The "S" is just personal preference.

Ashley McAdoo

Brillant! Thank you.

Paul Newcome

Happy to help. 👍️