Countif and contains

pageella

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!

pageella

That works!! Thank you so much for your help

Paul Newcome

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")

pageella

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

Volunteer event - in-person or virtual

Donation - monetary, cash, sponsorship

Donation - product, supplies, in-kind

Paul Newcome

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"))

pageella

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 =

Paul Newcome

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")))

pageella

That works!! Thank you so much for your help

Paul Newcome

Happy to help. 👍️