Countif and contains
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!
Best Answer
-
That works!! Thank you so much for your help
Answers
-
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")
-
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
-
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"))
-
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 =
-
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")))
-
That works!! Thank you so much for your help
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!