Sheet summary formula to add number of attendees based on a program type?

MorganFrem
edited 02/08/24 in Formulas and Functions

I am trying to count number of attendees (one column) based on a different column (program type) we have running ("A" vs "B" vs "C"). I am getting unparseable. Any suggestions? I am trying to use the below formula:

=COUNTIF((Program:Program, "A")) (AND(=COUNTIF(Number of Attendees:Number of Attendees, >0)

I have also tried:

=COUNTIFS(Number of Attendees:Number of Attendees, >0, [Program]:[Program], "A"])

Best Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    edited 02/08/24 Answer ✓

    If I understand correctly, this should give you the results you want for the "A" condition. I am still unclear about your comparison between A, B, C... do you plan to show the results for each in different columns?

    A couple of notes:

    Instead of attempting to combine multiple COUNTIF statements with AND, you can use a COUNTIFS instead. In fact, you can actually use COUNTIFS for checking only one condition. I personally always use COUNTIFS instead of COUNTIF.

    Anytime your column name includes a space (plus a few other conditions), you need to enclose it in square braces. I.E., [Number of Attendees] instead of Number of Attendees.

    =COUNTIFS([POET PROGRAM]:[POET PROGRAM], "A", [Number of Attendees]:[Number of Attendees], @cell > 0)

    If I am misinterpreting your request, please let me know.

  • MorganFrem
    Answer ✓

    Success! This is the final formula that worked:

    =SUMIF([POET Program]:[POET Program], "A", [Number of Attendees]:[Number of Attendees])

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    edited 02/08/24 Answer ✓

    If I understand correctly, this should give you the results you want for the "A" condition. I am still unclear about your comparison between A, B, C... do you plan to show the results for each in different columns?

    A couple of notes:

    Instead of attempting to combine multiple COUNTIF statements with AND, you can use a COUNTIFS instead. In fact, you can actually use COUNTIFS for checking only one condition. I personally always use COUNTIFS instead of COUNTIF.

    Anytime your column name includes a space (plus a few other conditions), you need to enclose it in square braces. I.E., [Number of Attendees] instead of Number of Attendees.

    =COUNTIFS([POET PROGRAM]:[POET PROGRAM], "A", [Number of Attendees]:[Number of Attendees], @cell > 0)

    If I am misinterpreting your request, please let me know.

  • MorganFrem
    edited 02/09/24

    @Carson Penticuff

    Thank you!

    For example, I would love to have the formula count how many participants are in each program "A". There are 25+ rows that include program 'A' versus another program 'B' (which I will also want to add the amount of participants for program 'B").

    Might I need a =SUM formula instead?

  • MorganFrem
    Answer ✓

    Success! This is the final formula that worked:

    =SUMIF([POET Program]:[POET Program], "A", [Number of Attendees]:[Number of Attendees])

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!