# Countif and contains

Options
✭✭

Hi,

I am trying to count the number if times a site code appears in a sheet, but I only want to count them if in another column they have a certain activity (eg volunteering or donation)

I then need to do this for multiple sheets!

The formula to do the first part is:

=COUNTIF({AMER - Ops CE Tracker - 2023 Site name}, [Site Name]@row+ COUNTIF({CEE - Ops CE Tracker - 2023 Site name}, [Site Name]@row) + COUNTIF({FR - Ops CE Tracker - 2023 Site Name}, [Site Name]@row) + COUNTIF({AU - Ops CE Tracker - 2023 Site name}, [Site Name]@row) + COUNTIF({ES - Ops CE Tracker - 2023 Site name}, [Site Name]@row) + COUNTIF({IT - Ops CE Tracker - 2023 Site name}, [Site Name]@row) + COUNTIF({JP - Ops CE Tracker - 2023 Site name}, [Site Name]@row) + COUNTIF({UK/IE - Ops CE Tracker - 2023 Site name}, [Site Name]@row)

BUT, this is counting all activity types, how do I count based on the activity?

thanks!

• ✭✭
Options

That works!! Thank you so much for your help

• ✭✭✭✭✭✭
Options

You would need to use a series of COUNTIFS (with the "S" on the end) to be able to use multiple range/criteria sets.

=COUNTIFS({AMER - Ops CE Tracker - 2023 Site name}, [Site Name]@row, {AMER - Ops CE Tracker - 2023 Activity}, "Volunteering")

• ✭✭
Options

Makes sense! How do I count it if it is any of the below:

Volunteer event - in-person or virtual

Donation - product, supplies, in-kind

• ✭✭✭✭✭✭
Options

You can do this in two ways. The way you choose is really personal preference, but it could be easier one way vs the other depending on how many different selections you have.

To count those three:

=COUNTIFS({AMER - Ops CE Tracker - 2023 Site name}, [Site Name]@row, {AMER - Ops CE Tracker - 2023 Activity}, OR(@cell = "Volunteer event - in-person or virtual", @cell = "Donation - monetary, cash, sponsorship", @cell = "Donation - product, supplies, in-kind"))

If you only have 4 selections, you can exclude the 4th and use

=COUNTIFS({AMER - Ops CE Tracker - 2023 Site name}, [Site Name]@row, {AMER - Ops CE Tracker - 2023 Activity}, @cell <> "4th Selection")

If you have 5 selections and just want to exclude the other two, you would use an AND function and structure it similar to the first one.

=COUNTIFS({AMER - Ops CE Tracker - 2023 Site name}, [Site Name]@row, {AMER - Ops CE Tracker - 2023 Activity}, AND(@cell <> "4th Selection", @cell <> "5th Selection"))

• ✭✭
Options

Super helpful thank you! Will it affect the formula that the question on the form is multi-select, so some cells could have more than one of the options ..?

Would there need to be CONTAINS somewhere? instead of =

• ✭✭✭✭✭✭
Options

Yes. In that case you would need a HAS function like so:

=COUNTIFS({AMER - Ops CE Tracker - 2023 Site name}, [Site Name]@row, {AMER - Ops CE Tracker - 2023 Activity}, OR(HAS(@cell, "Volunteer event - in-person or virtual"), HAS(@cell, "Donation - monetary, cash, sponsorship"), HAS(@cell, "Donation - product, supplies, in-kind")))

• ✭✭
Options

That works!! Thank you so much for your help

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!