Counting Formulas

Options

Hello,

I am looking to see if there is a formula that will collect the number of people who are coming to an event. I have a column that asks if they are attending a certain event. I know that I can collect the number of "Yes" by using the Formula =COUNTIF([SPS Reception?]:[SPS Reception?], CONTAINS("Yes, @cell)). What I want though is to know the total number if they select that they have guests coming with them (the next column). Do I need to change how I format the question to be able to sum up the numbers together or is there a formula that I can use to sum it up the way it is formatted?


Best Answer

  • Garrett Henke
    Garrett Henke ✭✭✭✭✭
    Answer ✓
    Options

    @Stefanie Sebastiao you can do this a couple of different ways.

    If you want to know the number of attendees as well as the number of additional guests you can use the following but you will need to adjust the additional guests to be numbers.

    • To count number of attendees =COUNTIF([SPS Reception?]:[SPS Reception?], "Yes")
    • To count number of plus ones =SUMIF([SPS Reception?]:[SPS Reception?], "Yes", [1,2,3,4,5?]:[1,2,3,4,5?])
    • Total attendance = Attendee Count + Plus One Count

    You could also make an additional column that sums all guests together and then use a sheet summary field to know the total attendance. This formula counts the yes as one attendee and then adds the number from the additional guests to it.

    =IF([SPS Reception?]@row = "Yes", 1 + [1,2,3,4,5?]@row, 0)

    No matter what I would change the 1,2,3,4,5? column values to be 0 1 2 3 4 5

Answers

  • Garrett Henke
    Garrett Henke ✭✭✭✭✭
    Answer ✓
    Options

    @Stefanie Sebastiao you can do this a couple of different ways.

    If you want to know the number of attendees as well as the number of additional guests you can use the following but you will need to adjust the additional guests to be numbers.

    • To count number of attendees =COUNTIF([SPS Reception?]:[SPS Reception?], "Yes")
    • To count number of plus ones =SUMIF([SPS Reception?]:[SPS Reception?], "Yes", [1,2,3,4,5?]:[1,2,3,4,5?])
    • Total attendance = Attendee Count + Plus One Count

    You could also make an additional column that sums all guests together and then use a sheet summary field to know the total attendance. This formula counts the yes as one attendee and then adds the number from the additional guests to it.

    =IF([SPS Reception?]@row = "Yes", 1 + [1,2,3,4,5?]@row, 0)

    No matter what I would change the 1,2,3,4,5? column values to be 0 1 2 3 4 5

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!