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!

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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")

  • pageella
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    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"))

  • pageella
    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 =

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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")))

  • pageella
    pageella ✭✭
    Answer ✓
    Options

    That works!! Thank you so much for your help

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!